Question

我期望做的是,根据整个数据库普遍存在的身份证,为我每个表的数据库获得一些元数据“状况”。

例如,如果我有<代码>customer Id 在整个数据库中,几乎所有表格的关键都是如此,我想提出一个问题,告诉我像什么东西。

Number of rows in each table based on that ID
Amount of space taken in each table based on that ID

我确信,我可以撰写一份储存的程序,每张桌上就座,然后对Id进行单独盘问,以收集浏览量,但能否用空间进行,或者是否在服务器上安装一些东西以获取这些信息?

最后,我可能建立一个表格,每星期或每月报告这一信息,以便我看到每个客户使用的趋势。

如果是这样的话,Im 查询服务器2017年或更高。

Answer 1

You can use the following code, which will construct a dynamic SQL query, that will add up the total DATALENGTH for every matching row in each table that has that column name.

It also takes into account clustered and non-clustered indexes. Do note that DATALENGTH is not a perfect representation of the size of indexes, for a number of reasons, but it should give you a good order-of-magnitude idea for each ID.

DECLARE @columnName sysname =  id ;
DECLARE @value int = 1;


DECLARE @sql nvarchar(max);

SELECT @sql = STRING_AGG(
  CONCAT(
     SELECT
  TableName =  ,
  QUOTENAME(t.name,     ),
   ,
  DataSize = SUM( ,
    c.Columns,
     )
FROM  ,
    QUOTENAME(s.name),
     . ,
    QUOTENAME(t.name),
     
WHERE  ,
    QUOTENAME(@columnName),
      = @value 
  ),
   
UNION ALL
 )
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
CROSS APPLY (
    SELECT
      Columns = STRING_AGG(CAST(
        CONCAT(
           ISNULL(DATALENGTH( ,
          QUOTENAME(c.name),
           ), 0) *  ,
          i.CountColumnInIndex
        )
        AS nvarchar(max)),   +  )
    FROM sys.columns c
    CROSS APPLY (
        SELECT
          CountColumnInIndex =
            IIF(
              MAX(CASE WHEN i.index_id = 1 AND ic.index_id IS NOT NULL THEN 1 END) = 1,
              COUNT(*),
              COUNT(CASE WHEN i.index_id <= 1 OR ic.index_id IS NOT NULL THEN 1 END)
            )
        FROM sys.indexes i
        LEFT JOIN sys.index_columns ic
          ON ic.index_id = i.index_id
         AND ic.object_id = t.object_id
         AND ic.column_id = c.column_id
        WHERE i.object_id = t.object_id
    ) i
    WHERE c.object_id = t.object_id
    GROUP BY ()
) c;

PRINT @sql; -- your friend

EXEC sp_executesql @sql,
  N @value int ,
  @value = @value;

db<>fiddle

友情链接