How to create a number of records tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to create a number of records tables?

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2008-12-08 : 11:01:10

I have a database containing more than one hundred tables.
I want to create a summary table listing table name and # of records
Is there a good way to do that?
Thanks.
Jeff

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 11:25:16

[code]

--Get all table names
BEGIN
Select Name into #tableNames
from sysobjects where xtype = 'U' order by 1

Create Table #TableCount (TableName Varchar(100), NoOfRowCount bigint)

declare @name varchar(100)

--declare a cursor to loop through all tables
declare cur cursor for select * from #tableNames
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
Insert #TableCount
exec ('select ''' + @name + ''' , count(1) from ' + @name)
print 'Fetching count of table : ' + @name
fetch next from cur into @name
end
close cur
deallocate cur
--show the data
Select * from #TableCount
drop table #tableNames
drop table #TableCount
END[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-12-08 : 11:28:15

no good way, no.
One problem is that, assuming the database is live, the rowcounts will never be up to date.
Also, accurate rowcounts can be expensive...by the time one table count is retrieved are others are out of date.

Given that, this will give you a quick and fairly accurate restult:


select object_name(id)
       ,max(rowcnt) 
from   sysindexes 
where  object_name(id) not like 'sys%'
group by id 
order by 1

Be One with the Optimizer
TG

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 12:25:56

[code]DECLARE @Tables TABLE
(
tableName SYSNAME,
recordCount BIGINT
)

INSERT @Tables
EXEC sp_msforeachtable 'SELECT ''?'', COUNT_BIG(*) FROM ?'

SELECT tableName,
recordCount
FROM @Tables
ORDER BY recordCount DESC,
tableName[/code]

E 12°55'05.63"
N 56°04'39.26"

jeff06
Posting Yak Master

166 Posts

Posted - 2008-12-08 : 13:30:36

Thanks.
Is there a way also to include column names in the table?
Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-08 : 14:07:07

See

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources