Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SysColumns Expand / Collapse
Author
Message
Posted Tuesday, January 6, 2009 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:26 AM
Points: 9, Visits: 25
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 ?
Post #630987
Posted Thursday, January 8, 2009 1:33 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 173, Visits: 1,707
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]






Post #632171
Posted Thursday, January 8, 2009 11:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:26 AM
Points: 9, Visits: 25
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
Post #632685
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse