Get table information with stored procedure? - 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

SQL Server Administration (2005)

Get table information with stored procedure?

Author

Topic

markshen2006
Starting Member

15 Posts

Posted - 2008-04-06 : 14:15:00

Hi,

I have a database with two handred tables.I want to get a list that have table name and numbers of record in each table.

Would you please give me a idea if you know how to do this with query or build-in stored procedure.

Thanks a lot

Mark

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-06 : 14:38:05

CREATE TABLE #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50))
SET NOCOUNT ON
INSERT #temp
Exec sp_MSforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
count(*) as col_count,
a.data_size
FROM #temp a
INNER JOIN INFORMATION_SCHEMA.COLUMNS b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(Replace(a.data_size, ' KB', '') as integer) desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources