September 20, 2007 at 9:22 am
Hi All,
I need to know how to write a sttore procedure to find out the COUNT of records having actual value and records with NULL for n Columns of a table.
Looping through the Coulms for certian criteria
finding the COUNT for
1 - Number of records with actual data
2 - Number of records with NULL
I need to generate a report for all the columns
Column1 | Count Of NULL | Count of Actual record
Coulmn2 |
ColumnN...
Thanks,
San
September 26, 2007 at 12:09 am
Count( * ) gives the number of records in the table
Count ( columnName ) gives you the count of rows with values in that column
the number of null values is then Count(*) - Count(ColumnName)
My suggestion:
NOTE: This is not 2k8 specific but the general idea works
As much as I hate suggesting dynamic SQL :w00t:
Create a Dynamic sql query that iterates through the columns of the table to giv you the count
Declare @ColName varchar(128)
, @sql nVarchar( 1000)
, @TotalRows int
If ( select id = object_id( 'Tempdb..#ResultTab' ) ) is not null
Drop table #ResultTab
Create Table #ResultTab (
ColName varchar(128)
, NonNullValue int
, NullValue int
)
Select @ColName = min( name) from sys.Columns where object_id = object_id( 'tSite' )
while @ColName is not null
Begin
Select @sql =
'Insert into #ResultTab
Select ''@ColName''
, Count( [@Colname] )
,0
From Tablename'
Select @sql = replace( @sql, '@ColName', @ColName )
Exec sp_ExecuteSQL @sql
Select @ColName = min( name) from sys.Columns where object_id = object_id( 'tablename' ) And Name > @ColName
End
Select @Totalrows = Count(*)
From tablename
Update #ResultTab
Set NullValue = @TotalRows - NonNullvalue
Select * From #ResultTab
Hope this helps
September 26, 2007 at 12:38 am
maybe the better way is to use the INFORMATION_SCHEMA views, but you'll get the idea using :
declare @Tabelname varchar(128)
set @Tabelname = 'T' -- Adjust to filter !!
-- print 'set transaction isolation level read uncommitted '
-- print 'go'
set nocount on
declare @Tbname varchar(128)
declare c1 cursor for
select O.name
from sysobjects o
inner join
syscolumns c
on o.id = c.id
where o.xtype = 'u'
and o.name like @Tabelname + '%'
-- and c.name like 'id%' -- Adjust if needed
group by o.name
order by 1
for read only
open c1
FETCH NEXT FROM c1
INTO @TbName
WHILE @@FETCH_STATUS = 0
BEGIN
print ' print ''-- Counting for ' + db_name() + '..' + @TbName + ''''
print ' Select count(*) as Number_Rows '
select ' , count (distinct [' + c.name + ']) as [Distinct_' + c.name + ']'
+ ' , sum (case when [' + c.name + '] is null then 1 else 0 end ) as [NULLCount_' + c.name + ']'
from sysobjects o
inner join
syscolumns c
on o.id = c.id
where o.type = 'u'
and o.name = @TbName
-- and upper(c.name) like 'ID%'
order by c.colid
print ' from ' + db_name() + '..' + @TbName + ' (NOLOCK) '
+ char(13) + '-- OPTION (MAXDOP 1 ) '
+ char(13) + 'GO '
FETCH NEXT FROM c1
INTO @TbName
END
-- Cursor afsluiten
CLOSE c1
DEALLOCATE c1[/i]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 4, 2007 at 12:11 am
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89941
N 56°04'39.16"
E 12°55'05.25"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply