Query Tables with Record Count

[bjr149]

I know this query is out there. Im looking to query a whole database and return a list of all tables with the count of records in each. Is this possible?

 

[DotNetGnat]

this link should answer all your questions...

http://www.code101.com/Code101/DisplayArticle.aspx?cid=18

-DNG

 

[mutley1]

Hey BJR,

Try this - Not taking any credit as I believe it was another helpful tek-tipper that did this to help me some time ago!!

declare @tabcnt int
declare @printline char (60)
select @tabcnt = count (*) from sysobjects where type = 'U'
 
If @tabcnt != 0
BEGIN
  select 'TABLE NAME'= convert (varchar (50), o.name), ROWS=i.rows
    from sysobjects o, sysindexes i
    where o.type = 'U'
      and o.id = i.id
      and i.indid in (0,1)
  order by o.name
END
 
select @printline = '(' + convert (varchar(10), @tabcnt) +
                       ' tables in ' + DB_NAME() + ')'
 
print ''
print @printline
GO

Cheers,

M.

 

[SQLDenis]

undocumented but fast

sp_msforeachtable 'select ''?'', count(*) from ?'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/