InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 16:55:38 GMT20http://www.sqlservercentral.com/Forums/Topic630987-169-1.aspxhi, yes it worked after a fashion. The totals were returned as columns though not rows. No matter it did the trick so a big thanks to all. I've never used information schema before so I'll try to remember this as a possibility for other situationsThu, 08 Jan 2009 11:23:51 GMT:O)http://www.sqlservercentral.com/Forums/Topic630987-169-1.aspxDid ALZDBA’s post from the other forum give you the result you wanted?Same code below but with the distinct removed, will eliminate nulls and what you have left is count of non null values in each column.[code]SELECT CASE t1.[ORDINAL_POSITION] WHEN 1 THEN 'SELECT ' ELSE ' , ' end + ' count( ['+ t1.[COLUMN_NAME] + '] ) as [Count_' + t1.[COLUMN_NAME] + ']' + CASE t1.[ORDINAL_POSITION] WHEN t2.[MAX_ORDINAL_POSITION] THEN ' from [' + t1.[TABLE_SCHEMA] + '].[' + t1.[TABLE_NAME] + '] ;' ELSE ' ' end FROM [INFORMATION_SCHEMA].[COLUMNS] t1 INNER JOIN ( SELECT [TABLE_NAME], [TABLE_SCHEMA], MAX([ORDINAL_POSITION]) AS MAX_ORDINAL_POSITION FROM [INFORMATION_SCHEMA].[COLUMNS] GROUP BY [TABLE_NAME], [TABLE_SCHEMA] )t2ON t1.[TABLE_NAME] = t2.[TABLE_NAME]AND t1.[TABLE_SCHEMA] = t2.[TABLE_SCHEMA]--WHERE t1.[TABLE_NAME] = 'mytable'ORDER BY t1.[TABLE_NAME], t1.[TABLE_SCHEMA], t1.[ORDINAL_POSITION][/code]Thu, 08 Jan 2009 01:33:12 GMTEric Klovninghttp://www.sqlservercentral.com/Forums/Topic630987-169-1.aspxFolks,Re-posted from 2005 forum - D'OHSay I have a table with columns, colA, colB, colC and colD and there are 20 rows in my table. I want to write a sort of dynamic script to show me a count of non-null columns.The result set would look something likecolA 5colB 20colC 10colD 1This example would show that each row had colb populated in each of the 20 rows.I know I could write a select that would generate some individual sql statements using syscolumns.name and syscolumns.id and I could then execute each of those statements but is there a way to do this more neatly ? Tue, 06 Jan 2009 13:47:13 GMT:O)