InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 08:46:30 GMT20http://www.sqlservercentral.com/Forums/Topic630941-1291-1.aspxContinued here - [url]http://www.sqlservercentral.com/Forums/Topic630987-169-1.aspx[/url]No more replies to this thread please.Tue, 06 Jan 2009 14:53:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic630941-1291-1.aspxapologies - just noticed it was the 2005 forumTue, 06 Jan 2009 13:43:53 GMT:O)http://www.sqlservercentral.com/Forums/Topic630941-1291-1.aspxPlease post SQL 2000-related questions in the SQL 2000 forums. If you post in the 2005 forums, you're very likely to get 2005-specific solutions.The Information schema exists in 2000 as well, and looks the same as it does in 2005.Tue, 06 Jan 2009 13:42:15 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic630941-1291-1.aspxSorry ALZDBA - I am only on sqlserver2000 !Liked the bike on yor profile pic !:cool:Tue, 06 Jan 2009 13:27:53 GMT:O)http://www.sqlservercentral.com/Forums/Topic630941-1291-1.aspxBetter would be to use the information schema views.Keep in mind that sql2005 only show object definitions you are granted to view.(view definition granted)Is this what you're looking for ?[code]SELECT CASE t1.[ORDINAL_POSITION] WHEN 1 THEN 'SELECT ' ELSE ' , ' end + ' count( distinct ['+ t1.[COLUMN_NAME] + '] ) as [Count_distinct_' + 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]Tue, 06 Jan 2009 13:22:52 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic630941-1291-1.aspxFolks,Say 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:09:07 GMT:O)