Dynamic SQL to get rows from information

Let’s say I’m looking for a specific column in my database so I have something like this

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

But I also want to know how many rows each table has, I was told I can do this using Dynamic SQL so I have this now

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

But this doesn't work, What I'm trying to do is query a table with the column I am looking for, with the table name, and number of rows in the table.

How can I fix this?

CodePudding user response：

Assuming that you are using SQL Server, here is a shorthand way using sp_msforeachtable.

DECLARE @ColumnName NVARCHAR(200) = 'ContactID'

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

INSERT INTO #T (ColumnName, TableName)
SELECT 
    CoulumnName = 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

CodePudding user response：

You can make use of SQL Server's dynamic management views to quickly obtain the row counts*.

Find all tables with a column named 'MyColumn' and their current rows:

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';

* Accurate except for frequently updated tables where there could be some lag