January 6, 2009 at 1:47 pm
Folks,
Re-posted from 2005 forum - D'OH
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 like
colA 5
colB 20
colC 10
colD 1
This 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 ?
January 8, 2009 at 1:33 am
Did 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.
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]
)t2
ON 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]
January 8, 2009 at 11:23 am
hi, 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 situations
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply