动态 SQL 从 information_schema 获取行

Question

假设我正在数据库中查找特定列，所以我有类似的内容

SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME like ‘%employeeid%’

但我也想知道每个表有多少行，有人告诉我可以使用动态 SQL 来完成此操作，所以我现在有了这个

DECLARE 
    @tableName NVARCHAR(MAX),
    @sql NVARCHAR(MAX),
    @colName NVARCHAR(MAX);

DECLARE CUR_TABLE CURSOR FOR
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS

OPEN CUR_TABLE
FETCH NEXT FROM CUR_TABLE
INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    
    SET @colName = '%employeeid%'
    SET @sql = 'SELECT COLUMN_NAME, TABLE_NAME, (SELECT COUNT(*) FROM ' + @tableName +') AS ROWS FROM INFORMATION_SCHEMA.COLUMNS where column_name like ' + ''' + @colName + ''';
    
    FETCH NEXT FROM CUR_TABLE
    INTO @tableName

END;

CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE



EXEC sp_executesql @sql

但这并不不起作用，我想做的是使用我正在查找的列、表名称和表中的行数查询表。

我该如何解决这个问题？

Answer 1

您可以利用 SQL Server 的动态管理视图快速获取行数*。

查找具有名为“MyColumn”的列及其当前行的所有表：

select Schema_Name(t.schema_id) schemaName, t.name TableName, s.row_count
from sys.columns c
join sys.tables t on t.object_id = c.object_id
join sys.dm_db_partition_stats s on s.object_id = c.object_id and s.index_id <= 1
where c.name='MyColumn';

* 准确，但频繁更新的表可能会出现一些滞后

Answer 2

以下使用 INFORMATION_SCHEMA、动态 SQL 和 STRING_AGG() 构建将返回单个结果集的查询。

DECLARE @ColumnName sysname = 'ProductID'

DECLARE @Newline VARCHAR(2) = CHAR(13) + CHAR(10)
DECLARE @SqlTemplate NVARCHAR(MAX) =
    + 'SELECT'
    + ' ColumnName = <ColumnNameString>,'
    + ' TableName = <TableSchemaAndNameString>,'
    + ' Rows = (SELECT COUNT(*) FROM <TableSchemaAndName>)'
    + @Newline
DECLARE @UnionSql NVARCHAR(100) = 'UNION ALL ' + @Newline

DECLARE @Sql NVARCHAR(MAX) = (
    SELECT STRING_AGG(
        REPLACE(REPLACE(REPLACE(
            @SqlTemplate
            , '<ColumnNameString>', QUOTENAME(C.COLUMN_NAME, ''''))
            , '<TableSchemaAndNameString>', QUOTENAME(C.TABLE_SCHEMA + '.' + C.TABLE_NAME, ''''))
            , '<TableSchemaAndName>', QUOTENAME(C.TABLE_SCHEMA) + '.' + QUOTENAME(C.TABLE_NAME))
        , @UnionSql)
        WITHIN GROUP(ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME)
    FROM INFORMATION_SCHEMA.TABLES T
    JOIN INFORMATION_SCHEMA.COLUMNS C
        ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND C.TABLE_NAME = T.TABLE_NAME
    WHERE T.TABLE_TYPE = 'BASE TABLE' -- Omit views
    AND C.COLUMN_NAME = @ColumnName
)

SET @Sql = @Sql + 'ORDER BY Rows DESC, TableName' + @Newline

--PRINT @Sql
EXEC (@Sql)

我通过添加 TABLE_SCHEMA 对其进行了一些概括，以便它可以与 AdventureWorks 数据库一起使用。请参阅此 db<>fiddle 以获取工作演示。还包括对旧版 SQL Server 版本使用 FOR XML 而不是 STRING_AGG 的等效逻辑。

Answer 3

假设您使用的是 SQL Server，这里有一个使用 sp_msforeachtable 的简写方法。

DECLARE @ColumnName NVARCHAR(200) = 'ContactID'

CREATE TABLE #T
(
    ColumnName NVARCHAR(200), 
    TableName NVARCHAR(200), 
    RecordCount INT
)

INSERT INTO #T (ColumnName, TableName)
SELECT 
    ColumnName = C.COLUMN_NAME, 
    TableName = '['+C.TABLE_SCHEMA+'].['+C.TABLE_NAME+']' 
FROM 
    INFORMATION_SCHEMA.COLUMNS C
WHERE 
    C.COLUMN_NAME LIKE '%' + @ColumnName + '%'

EXEC SP_MSFOREACHTABLE 'IF EXISTS(SELECT * FROM #T WHERE TableName = ''?'') UPDATE #T SET RecordCount = (SELECT COUNT(*) FROM ? ) WHERE TableName = ''?'''

SELECT 
    ColumnName,TableName,
    TableType = CASE 
                    WHEN RecordCount IS NULL 
                        THEN 'View' 
                        ELSE 'Table' 
                END,
    RecordCount
FROM 
    #T
ORDER BY
    CASE WHEN RecordCount IS NULL THEN 'View' ELSE 'Table' END

DROP TABLE #T