Table list and sizes - SQL Server Forums

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)

Table list and sizes

Author

Topic

Sean_B
Posting Yak Master

111 Posts

Posted - 2010-01-04 : 13:51:34

Hi,

a user asked me if it is possible to get a list of tables with the row count beside the name, and I supplied him with the code below to do this.

The user then expressed suprise that there wan't built in functionality to do this.

Just to check, can anyone tell me whether there is any built in functionality / SSMS gui to do what I've done below, or is this the best way of doing it ?

------------------------------------------------
CREATE PROCEDURE usp_ListTableRows
as
begin

DECLARE @db VARCHAR(100)

SET @db = 'reference'

DECLARE @max INT,@count INT,@sql NVARCHAR(MAX)

IF OBJECT_ID('tempdb..#list') IS NOT NULL DROP TABLE #list
IF OBJECT_ID('tempdb..##temp') IS NOT NULL DROP TABLE ##temp

CREATE table #list (NAME varchar(100),ROWS int,reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100) )

SELECT @sql = 'SELECT IDENTITY(INT,1,1) id,''' + @db + '.dbo.''' + ' + name AS name INTO ##temp FROM ' + @db + '.sys.tables'

exec (@sql)

SELECT @count = 0, @max = MAX(id) FROM ##temp
WHILE @count < @max
BEGIN

SET @count = @count + 1

SELECT @sql = 'INSERT INTO #list EXEC sp_spaceused ''' + t.name + '''' from ##temp t WHERE t.id = @count

EXEC(@sql)

END

SELECT NAME,ROWS FROM #list ORDER BY NAME

END

Sean

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-04 : 13:53:41

I use the SSMS "Disk Usage by Top Tables" report. Just right-click on the database, go to Reports, and then select it from the Custom Reports... option. After you've run it once on your client machine, it'll now appear directly off the Reports menu item.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-04 : 14:01:44

SELECT SCHEMA_NAME(t1.schema_id) AS [schema], t1.name AS [table], i1.rows AS [row_count]
FROM sys.tables AS t1 INNER JOIN sys.sysindexes AS i1 ON t1.object_id = i1.id AND i1.indid < 2

Jack Vamvas
--------------------
http://www.ITjobfeed.com

Sean_B
Posting Yak Master

111 Posts

Posted - 2010-01-04 : 14:14:10

Thank you very much.

Sean

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-05 : 01:36:48

quote:
Originally posted by jackv

SELECT SCHEMA_NAME(t1.schema_id) AS [schema], t1.name AS [table], i1.rows AS [row_count]
FROM sys.tables AS t1 INNER JOIN sys.sysindexes AS i1 ON t1.object_id = i1.id AND i1.indid < 2

Jack Vamvas
--------------------
http://www.ITjobfeed.com

You should run

DBCC UPDATEUSAGE (0)

to get accuracy in row counts

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-01-05 : 01:44:09

DBCC UPDATEUSAGE only needs to be run if you upgraded from an earlier version such as 2000. On newly created databases in 2005, it never needs to be run as 2005 keeps the information correct. On upgraded databases, it only needs to be run once and then 2005 will keep it up to date.

So you typically do not need to run DBCC UPDATEUSAGE on 2005 or 2008.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-05 : 02:01:45

quote:
Originally posted by tkizer

DBCC UPDATEUSAGE only needs to be run if you upgraded from an earlier version such as 2000. On newly created databases in 2005, it never needs to be run as 2005 keeps the information correct. On upgraded databases, it only needs to be run once and then 2005 will keep it up to date.

So you typically do not need to run DBCC UPDATEUSAGE on 2005 or 2008.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Yes. I fotgot to notice that this was posted in SQL Server 2005 forum

Madhivanan

Failing to plan is Planning to fail

Sean_B
Posting Yak Master

111 Posts

Posted - 2010-01-05 : 04:53:05

Thanks for your input guys, here's another one that someone supplied.

SELECT tbl.name 'TableName',
Coalesce( (SELECT SUM(s.rows) FROM sys.partitions s
WHERE s.object_id = tbl.object_id AND s.index_id < 2)
, 0)
AS 'RowCount'
FROM sys.tables as tbl
GROUP BY tbl.name, tbl.object_id
ORDER BY tbl.name

Sean

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-01-06 : 07:41:07

This last one has a higher cost , but added avantage of Coalesce ,

Jack Vamvas
--------------------
http://www.ITjobfeed.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources