Query to return table rowcounts & identity values in SQL Server

Sometimes when you are doing administrative or investigative work in your database you need to get an up-to-date idea of the the row count and Identity key values (for those tables having them). I created the following script to this quickly for whichever database you are currently using in SQL Server.
It doesn’t work very well for schema partitioned tables like those you’d find in Microsoft’s distributed example database ‘Adventureworks’ but for databases with only DBO tables it should work fine. As always, feel free to use/modify this script to your heart’s content.

/*
-run the following to get a row count and the max primary key for an identity column of all DBO user tables with Identity keys
-also run the same without the max primary key for all DBO user tables without Identity keys
-doesn’t work well with databases with non DBO schemas i.e.: AdventureWorks will not show very many rows
*/

declare @tbltmpStats table (
Identity_Tables int identity (1, 1) not null ,
Table_Name nvarchar(200),
Num_Rows int,
Identity_Max int)
declare @objectid int;
declare @objectname nvarchar(200);
declare @sql1 nvarchar(4000)
declare @objectcursor cursor

set @objectcursor=cursor for select o.object_id from sys.objects o
where type=’u’
and objectproperty(o.object_id, N’TableHasIdentity’)=1
and o.schema_id = 1
order by o.name
open @objectcursor
fetch next from @objectcursor into @objectid
while (@@fetch_status=0) begin
select @objectname=name from sys.objects where object_id=@objectid
set @sql1 = ‘select ”’ + cast(@objectname as nvarchar(200)) + ”’, count(*), max(identitycol) from ‘ + @objectname
insert into @tbltmpstats (Table_Name, Num_Rows, Identity_Max)
exec (@sql1)
fetch next from @objectcursor into @objectid
end
close @objectcursor
select * from @tbltmpstats

declare @tbltmpStats2 table (
NonIdentity_Tables int identity (1, 1) not null ,
Table_Name nvarchar(200),
Num_Rows int
)
set @objectcursor=cursor for select o.object_id from sys.objects o
where type=’u’
and objectproperty(o.object_id, N’TableHasIdentity’)=0
and o.schema_id = 1
order by o.name
open @objectcursor
fetch next from @objectcursor into @objectid
while (@@fetch_status=0) begin
select @objectname=name from sys.objects where object_id=@objectid
set @sql1 = ‘select ”’ + cast(@objectname as nvarchar(200)) + ”’, count(*) from ‘ + @objectname
insert into @tbltmpstats2 (Table_Name, Num_Rows)
exec (@sql1)
fetch next from @objectcursor into @objectid
end
close @objectcursor
select * from @tbltmpstats2
deallocate @objectcursor

About these ads