looping through columns for rowcount

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

looping through columns for rowcount

Author

Topic

santhoshnataraj
Starting Member

4 Posts

Posted - 2007-09-25 : 06:32:45

Hi All,

I have a table with 260 columns. I want to find out each individual column datum count for

1 - Number of rows having NULL data.
2 - Number of rows NOT NULL data (having good data).

Example:
Pivot | Number of | Number of
Table | Null data | Not NULL data

Column 1 | 40 | 60
Column 2 | 35 | 55
Column N | 20 | 80

Thanks,
Santhosh

Analyst

Kristen
Test

22859 Posts

Posted - 2007-09-25 : 06:58:45

[code]
SELECT 'SELECT ''Row Count'', COUNT(*) FROM MyTable UNION ALL' UNION ALL
SELECT TOP 10
'SELECT ''' + COLUMN_NAME + ''', COUNT([' + COLUMN_NAME + ']) FROM MyTable UNION ALL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'
[/code]
Run that, cut & paste the SQL generated to your execute window, remove the final "UNION ALL" and run the result.

The first row is the number of rows in the table, the subsequent ones are the number of NOT NULL rows for each table.

Kristen

santhoshnataraj
Starting Member

4 Posts

Posted - 2007-09-25 : 07:27:33

Hi Kristen,

I tried to execute this code it's giving the syntax error at two places
1 - ', COUNT(*) FROM MyTable UNION ALL'
2 - '+'

I tried to replace MyTable to my Table name 'ABC'

Please let know the correction...

Cheers,
San

quote:
Originally posted by Kristen

SELECT 'SELECT ''Row Count'', COUNT(*) FROM MyTable UNION ALL' UNION ALL
SELECT TOP 10
'SELECT ''' + COLUMN_NAME + ''', COUNT([' + COLUMN_NAME + ']) FROM MyTable UNION ALL'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

Run that, cut & paste the SQL generated to your execute window, remove the final "UNION ALL" and run the result.

The first row is the number of rows in the table, the subsequent ones are the number of NOT NULL rows for each table.

Kristen

Analyst

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 07:44:45

[code]DECLARE @SQL VARCHAR(8000)

SELECT @SQL = ''

SELECT @SQL = @SQL + ' UNION ALL SELECT ' + QUOTENAME(COLUMN_NAME, '''')
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS ColumnName,' ELSE ',' END
+ CONVERT(VARCHAR, ORDINAL_POSITION)
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS Position,' ELSE ',' END
+ 'CASE WHEN ' + QUOTENAME(COLUMN_NAME) + ' IS NULL THEN 1 ELSE 0 END'
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS NullData' ELSE '' END
+ ' FROM ' + QUOTENAME(TABLE_NAME)
FROM (
SELECT TOP 100 PERCENT
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Test'
ORDER BY ORDINAL_POSITION
) AS d

SELECT @SQL = STUFF(@SQL, 1, 11, 'SELECT ColumnName, SUM(NullData) AS NullData, COUNT(*) - SUM(NullData) AS NotNullData, COUNT(*) AS TotalData FROM ('),
@SQL = @SQL + ') AS d GROUP BY ColumnName ORDER BY MIN(Position)'

--PRINT @SQL
EXEC (@SQL)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 08:23:52

[code]DECLARE @SQL VARCHAR(8000)

SELECT @SQL = ''

SELECT @SQL = @SQL + ' UNION ALL SELECT ' + QUOTENAME(COLUMN_NAME, '''')
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS ColumnName,' ELSE ',' END
+ CONVERT(VARCHAR, ORDINAL_POSITION)
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS Position,' ELSE ',' END
+ 'COUNT(' + QUOTENAME(COLUMN_NAME) + ')'
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS NotNullData,' ELSE ',' END
+ 'COUNT(*)'
+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS TotalData' ELSE '' END
+ ' FROM ' + QUOTENAME(TABLE_NAME)
FROM (
SELECT TOP 100 PERCENT
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblFriends'
ORDER BY ORDINAL_POSITION
) AS d

SELECT @SQL = STUFF(@SQL, 1, 11, 'SELECT ColumnName, TotalData - NotNullData AS NullData, NotNullData AS NotNullData, TotalData FROM ('),
@SQL = @SQL + ') AS d ORDER BY Position'

--PRINT @SQL
EXEC (@SQL)[/code]

E 12°55'05.25"
N 56°04'39.16"

santhoshnataraj
Starting Member

4 Posts

Posted - 2007-09-25 : 09:14:57

Hi Peso,

Thank you for the script.
I am facing a small issue. If the table contains few columns then it works fine, but for the other tables has more than 100 columns max 260 columns for this it gives syntax error in this

STUFF(@SQL, 1, 11, 'SELECT ColumnName

Please let me know how to go about it.

Thanks,
San

quote:

Originally posted by Peso

DECLARE	@SQL VARCHAR(8000)

SELECT	@SQL = ''

SELECT		@SQL = @SQL	+ ' UNION ALL SELECT ' + QUOTENAME(COLUMN_NAME, '''')
				+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS ColumnName,' ELSE ',' END
				+ CONVERT(VARCHAR, ORDINAL_POSITION)
				+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS Position,' ELSE ',' END
				+ 'COUNT(' + QUOTENAME(COLUMN_NAME) + ')'
				+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS NotNullData,' ELSE ',' END
				+ 'COUNT(*)'
				+ CASE WHEN ORDINAL_POSITION = 1 THEN ' AS TotalData' ELSE '' END
				+ ' FROM ' + QUOTENAME(TABLE_NAME)
FROM	(
		SELECT		TOP 100 PERCENT
				TABLE_NAME,
				COLUMN_NAME,
				ORDINAL_POSITION
		FROM		INFORMATION_SCHEMA.COLUMNS
		WHERE		TABLE_NAME = 'tblFriends'
		ORDER BY	ORDINAL_POSITION
	) AS d

SELECT	@SQL = STUFF(@SQL, 1, 11, 'SELECT ColumnName, TotalData - NotNullData AS NullData, NotNullData AS NotNullData, TotalData FROM ('),
	@SQL = @SQL + ') AS d ORDER BY Position'
	
--PRINT @SQL
EXEC (@SQL)

E 12°55'05.25"
N 56°04'39.16"

Analyst

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 09:20:59

It might happen when the @SQL string is larger than 8000 characters.

E 12°55'05.25"
N 56°04'39.16"

santhoshnataraj
Starting Member

4 Posts

Posted - 2007-09-25 : 09:26:48

Hi Peso,

Is there any alternative.

Also I wanted to know if I can give condition insted of all the records I wanted the records from a specific date.

Thanks,
San

quote:
Originally posted by Peso

It might happen when the @SQL string is larger than 8000 characters.

E 12°55'05.25"
N 56°04'39.16"

Analyst

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-25 : 09:56:47

OK, enough

What possible value is this information going to give you?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-09-26 : 01:37:58

Heh... that's the FIRST question I ask when someone posts a question like this. And, I normally follow that with a hearty "You need to normalize your data".

It's usually someone trying to use SQL like a spreadsheet

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 01:53:55

quote:
Originally posted by santhoshnataraj

Is there any alternative.

Try the smaller code posted 09/25/2007 : 08:23:52

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 05:46:16

"What possible value is this information going to give you?"

I have a KristenReverseEngineerSproc which is the first thing I run on an "unknown" database that tells me the NULL count, along with varchar columns that exclusively contain valid dates

and stuff like that. And what's not indexed that maybe should be. Takes about half an hour to increase the performance of a crappy server by about 100 fold, I just have to be careful to make it look like it took all day to do so that the customer thinks there was some blood, sweat and toil involved

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 06:14:51

Oh, you are the one that introduced WAITFOR in T-SQL?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 06:20:24

Blimey, I don;t even have to post the code and you can tell me the performance is terrible ... that's impressive Peso