query help - SQL Server Forums

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

SQL Server Development (2000)

query help

Author

Topic

Llama_master
Starting Member

3 Posts

Posted - 2006-01-20 : 09:17:14

I'm trying to run a query that will display the column name and the number of non-null records in the column. I've noticed that the select count() function always seems to act as a 'select count(*)' when using a variable.

Example: 'Select count(column_name) from table_owner.table_name' will return the number of non-null records for the column_name. However, 'Select count(@column_name) from table_owner.table_name' always returns the number of records in the table.

Here's what I'm trying to run:

---------begin------------

declare @columnname varchar(25)
declare columnname_cur cursor for
SELECT name
FROM syscolumns
WHERE (id = 553794522)

open columnname_cur
fetch next from columnname_cur into @columnname

while @@fetch_status = 0
BEGIN

select @columnname, count(@columnname) from table_owner.table_name
fetch next from columnname_cur into @columnname
END

close columnname_cur
deallocate columnname_cur

----------end----------

Any help would be appreciated. The table has 222 columns and I really don't want to manually do a count() on each one manually.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-01-20 : 10:01:06

declare @tblname varchar(128)
select @tblname = 'mytbl'
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)
select @sql1 = coalesce(@sql1 + ',', 'select ') + 'cnt_' + c.name + '=sum(case when ' + c.name + ' is null then 0 else 1 end)'
from syscolumns c where id = object_id(@tblname)
select @sql2 = ' from ' + @tblname

exec (@sql1+@sql2)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Llama_master
Starting Member

3 Posts

Posted - 2006-01-20 : 10:39:53

Thanks, nr.

However, your query is giving me the following:

Invalid syntax near '='

Note that I had to adjust the query as follows due to the use of both leading and trailing underscores in the column names:

declare @tblname varchar(128)
select @tblname = '_SMDBA_._TELMASTE_'
declare @sql1 varchar(8000)
declare @sql2 varchar(8000)

select @sql1 = coalesce(@sql1 + ',', 'select ') + 'cnt_[' + c.name + '] = sum(case when ['
+ c.name + '] is null then 0 else 1 end)'
from syscolumns c where id = object_id(@tblname)
select @sql2 = ' from ' + @tblname

exec (@sql1+@sql2)

I'm getting the following results. This is pulled directly from SQL Query Analyzer using 'select (@sql1+@sql2)

select cnt_[_EMAILID_] = sum(case when [_EMAILID_] is null then 0 else 1 end),cnt_[_GROUP_] = sum(case when [_GROUP_] is null then 0 else 1 end),cnt_[_INACTIVE_:] = sum(case when [_INACTIVE_:] is null then 0 else 1 end),cnt_[_OWNER_] = sum(case when [_OWNE

Notice it's getting cut off on the last sum and this is almost certainly the cause of the syntax error. We initialized the variable with 8000 characters, though, so it shouldn't be cutting off this soon, should it?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-01-20 : 12:00:22

No, that's just QA results quiting at 255 chars, change it in you options to 4000 or something..

Move your bracket...

select @sql1 = coalesce(@sql1 + ',', 'select ') + '[cnt_' + c.name + '] = sum(case when ['

Llama_master
Starting Member

3 Posts

Posted - 2006-01-20 : 12:13:22

Got it working. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources