CODEWITHSUNDEEP
sqlsql-server
user2585435
user2585435

Reputation: 131

SQL query to dynamically COUNT(FIELD) for all fields of table X

This should be such an easy thing, but it has me totally stumped.

You can easily return the count of each field of a table manually, with oneliners such as:

select count(FIELD1) from TABLE1   --42,706
select count(FIELD5) from TABLE1   --42,686
select count(FIELD9) from TABLE1   --2,918

This is slow and painful if you want to review several dozen tables the same way, and requires you to know the names of the fields in advance.

How handy would it be to have a script you can connect to any database, simply feed it a table name, and it will automatically return the counts for each field of that table?

Seems you can get half the work done with:

select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TABLE1'

Something is flawed even with my barebones approach (explicitly hitting one field instead of them all):

declare @TABLENAME varchar(30), @FIELDNAME varchar(30)
set @TABLENAME = 'TABLE1'

set @FIELDNAME = (select top 1 COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TABLENAME
and COLUMN_NAME = 'FIELD9')

select @FIELDNAME, count(@FIELDNAME) from TABLE1

The result is 42,706. Recall from my example above that FIELD9 only contains 2,918 values.

Even if that wasn't a problem, the more dynamic query would replace the last line with:

select @FIELDNAME, count(@FIELDNAME) from @TABLENAME

But SQL Server returns:

Must declare the table variable "@TABLENAME".

So I can avoid that by restructuring the query with a temp table:

declare @FIELDNAME varchar(30)

set @FIELDNAME = (select top 1 COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TABLE1'
and COLUMN_NAME = 'FIELD9')

if OBJECT_ID('TEMPDB..#TEMP1') is not null
    drop table #TEMP1

select *
into #TEMP1
from TABLE1  --still not exactly dynamic!

select @FIELDNAME, count(@FIELDNAME) from #TEMP1

But that still brings us back to the original problem of returning 42,706 instead of 2,918.

I am running SQL Server 2008 R2, if it makes any difference.

Upvotes: 2

Views: 4224

Answers (2)

Eric
Eric

Reputation: 5743

Just set the @TargetTableName will do the job

DECLARE @TargetTableName sysname = '*'

SET NOCOUNT ON
DECLARE @TableName sysname, @ColumnName sysname, @Sql nvarchar(max)
DECLARE @TableAndColumn table
(
    TableName sysname,
    ColumnName sysname
)

DECLARE @Result table
(
    TableName sysname,
    ColumnName sysname,
    NonNullRecords int
)

INSERT @TableAndColumn
SELECT o.name, c.name FROM sys.objects o INNER JOIN sys.columns c ON o.object_id = c.object_id 
WHERE (o.name = @TargetTableName OR @TargetTableName = '*') AND o.type = 'U' AND c.system_type_id NOT IN (34, 35, 99) -- 34:image 35:text 99:ntext
ORDER BY c.column_id


DECLARE column_cursor CURSOR FOR SELECT TableName, ColumnName FROM @TableAndColumn
OPEN column_cursor

FETCH NEXT FROM column_cursor 
INTO @TableName, @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT @Sql = 'SELECT ''' + @TableName + ''' AS TableName, ''' + @ColumnName + ''' AS ColumnName, COUNT([' + @ColumnName + ']) AS NonNullRecords FROM [' + @TableName + ']'
    print @Sql

    INSERT @Result
    EXEC (@Sql) 

    FETCH NEXT FROM column_cursor 
    INTO @TableName, @ColumnName
END 
CLOSE column_cursor;
DEALLOCATE column_cursor;

SET NOCOUNT OFF

SELECT * FROM @Result

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

Your query:

SELECT @FIELDNAME, COUNT(@FIELDNAME) FROM TABLE1

does not count FIELD9, @FIELDNAME is treated as a constant. It's like doing a COUNT(*).

You should use dynamic sql:

DECLARE @sql VARCHAR(MAX)

SET @sql = 'SELECT ''' + @fieldName + ''', COUNT([' + @fieldName + ']) FROM [' + @tableName + ']'

EXEC(@sql)

To get all columns and return it in a single result set without using a Temporary Table and CURSOR:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql +
'SELECT ''' + COLUMN_NAME + ''' AS ColName, COUNT([' + COLUMN_NAME + ']) FROM [' + @tableName + ']' + CHAR(10) +
'UNION ALL' + CHAR(10)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName

SELECT @sql = LEFT(@sql, LEN(@sql) - 10)
EXEC(@sql)

Upvotes: 2

Related Questions