SQL script to show number of records in all tables in a database

It seems to be quite often that I need to dive into an SQL Server database that I don't know anything about and have to find some piece of data. Generally this means looking at the names and relationships of the tables to find the logical place the data I'm looking for might be found, scrolling through records in numerous tables, etc. Sometimes I will use profiler to see what sql queries are being passed to the server when I do certain things in a client application. Profiler is a great tool for that by the way, I love it - but that's another topic.

Sometimes just knowing which tables have the most records can help you quickly find what you're looking for. If I'm trying to find where the log detail or history records for a particular application are kept, chances are the table holding the log detail is going to have a lot of records. But there could be hundreds of tables in the database that may or may not be named with logical or obvious names (or even in a different language), so scrolling through the records table by table is impractical sometimes.

So I decided to write a script to show me the record count of all tables (excluding system tables) in a database. It's not the most efficient, but it's quick and dirty, and it works well. I tried to think of a way to use database statistics to easily come up with this, but off the top of my head couldn't come up with anything - "dbcc show_statistics" for example doesn't really give me what I want, and you have to give it a particular table.

The query below accomplishes this for me. It's really pretty simple. I just connect to the database I want to look at and give the script the owner of the tables. That is one limitation - it will only work for all the tables owned by the owner you specify. Generally the user database tables are all owned by one owner (and this is often just dbo), but you could run it multiple times if there were different table owners. Basically it queries the sysobjects table for all tables in the database, then using a cursor loops through the records, executing a dynamic sql statement to insert the record count of each table into a temporary table. It then queries that temporary table and drops it. It could easily be incorporated into a stored procedure, but I just needed something I could run in query analyzer to give me the results I needed. Like I say, it's not the most efficient, and there's probably an SQL Server guru out there that has a much better solution (and I welcome your comments), but it works. Here is the query:


declare @tableName nvarchar(50)
declare @sql nvarchar(1000)
declare @owner as nvarchar(20)

set @owner = 'dbo'

create table #recordCounts (
tablename nvarchar(50),
recordCount int
)

declare c1 cursor for
select name from sysobjects where xtype='U'

open c1

fetch next from c1 into @tableName

while @@fetch_status = 0
begin
select @sql = 'insert into #recordCounts select ''' + quotename(@tableName) + ''',count(*) from ' + @owner + '.' + quotename(@tableName)
exec sp_executesql @sql
fetch next from c1 into @tableName
end

close c1
deallocate c1

select * from #recordCounts order by recordCount desc

drop table #recordCounts