SQL Scripts: Record Counts

As I'm populating a sample set of data in a SQL Server project, I needed a quick way of identifying what tables I had missed.

Here's what I came up with (but a much faster way is noted below using the sys tables).

--- Identifies zero record count tables
declare @vtb varchar(200)
declare @vsc varchar(200)

declare x cursor for select table_schema,table_name from information_schema.tables
open x
fetch next from x into @vsc,@vtb
declare @vcnt int
print 'declare @vcnt int'
while @@FETCH_STATUS = 0
begin
set @vcnt = 0
print 'set @vcnt = 0'
print 'select @vcnt=count(*) from ' + @vsc+'.['+@vtb + ']'
print 'if @vcnt=0 print ''' + @vsc+'.'+@vtb + ''''


fetch next from x into @vsc,@vtb
end
close x
deallocate x

Note, that the script does support the schema tag but it does expect you to already be in the database (via USE).

This generates the output into the message window where you can then take it and run it.

You could also simply pipe it to a variable and then execute that variable.

It's not fancy, but it does work so you may also find it of use.

The other approach I've seen used is one where you use the sys.indexes table.

--Code modified from original posting on SQLServerCentral.Com
--URL: http://www.sqlservercentral.com/scripts/Miscellaneous/30324/
SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
FROM sysobjects o, sysindexes i
WHERE i.id = o.id
AND indid IN(0,1)
AND xtype = 'u'
AND o.name <> 'sysdiagrams'

I have tried to stay away from the sysobjects table primarily since Microsoft doesn't advocate their use (although I can't find a posting about this), even though they are typically the fastest and easiest ways to get to real data.

Update:
Here's the similar script but using sp_spaceused to see how much space the actual tables are taking up.

declare @vtb varchar(200)
declare @vsc varchar(200)

declare x cursor for select table_schema,table_name from information_schema.tables
open x
fetch next from x into @vsc,@vtb
declare @vcnt int
print 'declare @vcnt int'
while @@FETCH_STATUS = 0
begin
set @vcnt = 0
select @vcnt = rowcnt FROM sysobjects o, sysindexes i
WHERE i.id = o.id
AND indid IN(0,1)
AND xtype = 'u'
AND o.name =@vtb
if @vcnt>50000
begin
print 'retrieving for ' + @vtb + ' rows: ' + convert(varchar(10),@vcnt)
exec sp_spaceused @vtb
end

fetch next from x into @vsc,@vtb
end
close x
deallocate x