動態SQL從information_schema中獲取行

假設我正在我的資料庫中尋找一個特定的列，所以我有這樣的東西

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

但這不起作用，我想做的是用我正在尋找的列、表名和表中的行數查詢一個表。

我怎樣才能解決這個問題？

uj5u.com熱心網友回復：

您可以利用 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';

*準確，除了經常更新的表格可能會有一些滯后

uj5u.com熱心網友回復：

下面使用 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 的等效邏輯。

uj5u.com熱心網友回復：

假設您使用的是 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

標籤：sql tsql 动态 sql 信息图式

上一篇：為表2的每個值選擇表1的每個不同值

下一篇：30道關于linux的基礎命令小題，先練練手