x

Index and Table size across the Server

Dear All,

I need to get the Table & Index size and the number rows for all the databases on my server. What is the best way to get the data. I am assuming that I can use Catalog views, is it right? I am not sure where to start. I have found couple of Stored Procedures online...sp_spaceused and sp_msForEachTable but they just work for one database and I am looking for entire server.

Thanks in advance.

Cheers - DV
more ▼

asked Dec 08, 2011 at 02:08 AM in Default

OnlyGraphite gravatar image

OnlyGraphite
41 6 6 7

Please can someone give more inputs on SQL Server meta data?

Thanks in advance.

Cheers - DV
Dec 08, 2011 at 03:00 AM OnlyGraphite
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
Another place to start, if you need to use this as a learning exercise, and you're using a more recent version of SQL Server (2005 and above), is http://msdn.microsoft.com/en-us/library/ms188917.aspx - the DMV for physical index statistics, which includes the number of (8k) pages used by an index.
more ▼

answered Dec 08, 2011 at 03:06 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

Thank you again.:)
Dec 08, 2011 at 03:40 AM OnlyGraphite
(comments are locked)
10|1200 characters needed characters left
more ▼

answered Dec 08, 2011 at 02:18 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

Thank you ThomasRushton. This will be very useful. However, I am planning to learn SQL Server Meta Data with this task. So I am looking for some valuable suggestions. I am newbie to SQL Server and this excerise will help me to understand the meta data.

Thanks again. I really appreciate your help.
Dec 08, 2011 at 02:25 AM OnlyGraphite
(comments are locked)
10|1200 characters needed characters left

Please use given below query-

DECLARE @sql VARCHAR(200) DECLARE @SourceDB VARCHAR(50) CREATE TABLE #ServerSpaceUsed (DB_Name VARCHAR(100),Table_Name VARCHAR(128), Rows VARCHAR(11), Reserved VARCHAR(18),Data VARCHAR(18),Index_size VARCHAR(18),Unused VARCHAR(18))

DECLARE CUR_SR_SPACE CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE name NOT IN('master','tempdb','model','msdb') OPEN CUR_SR_SPACE FETCH NEXT FROM CUR_SR_SPACE INTO @SourceDB WHILE @@FETCH_STATUS =0 BEGIN

CREATE TABLE #tables(name VARCHAR(128))

SET @sql = 'INSERT #tables SELECT TABLE_NAME FROM ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''' EXEC(@sql)

DECLARE @NAME VARCHAR(128)
SET @name = ''

CREATE TABLE #DBSpaceUsed 
    (Table_Name VARCHAR(128), Rows VARCHAR(11), 
       Reserved VARCHAR(18),Data VARCHAR(18),Index_size VARCHAR(18),Unused VARCHAR(18))

WHILE EXISTS (SELECT * FROM #tables WHERE name > @name)
BEGIN

    SELECT @name = MIN(name) FROM #tables WHERE name > @name
    SELECT @sql = 'EXEC ' + @SourceDB + '..sp_executesql N''INSERT #DBSpaceUsed exec sp_spaceused ' + @name + ''''
    EXEC (@sql)
END

INSERT INTO #ServerSpaceUsed(DB_Name,Table_Name,Rows,Reserved,Data,Index_size,Unused)
SELECT @SourceDB,Table_Name,Rows,Reserved,Data,Index_size,Unused FROM #DBSpaceUsed

drop table #tables
drop table #DBSpaceUsed

FETCH NEXT FROM CUR_SR_SPACE INTO @SourceDB END CLOSE CUR_SR_SPACE DEALLOCATE CUR_SR_SPACE

SELECT * FROM #ServerSpaceUsed DROP TABLE #ServerSpaceUsed

more ▼

answered Dec 08, 2011 at 03:12 AM

Amardeep gravatar image

Amardeep
1.3k 87 88 89

This is cool. Althought I dont know how to use cursors. But I will explore them.

Thanks again for you help.
Dec 08, 2011 at 03:40 AM OnlyGraphite
I am having issue with table name which contains space (Table Name) in the above query. I need to add the [] in here but I am not sure... please can you help?
Dec 08, 2011 at 04:19 AM OnlyGraphite

You should use QUOTENAME. Looking at the script you should change it like this

SELECT @sql = 'EXEC ' + @SourceDB + '..sp_executesql N''INSERT #DBSpaceUsed exec sp_spaceused ' + QUOTENAME(@name) + ''''
Dec 08, 2011 at 04:26 AM Sacred Jewel

Please change given below script line-

SET @sql = 'INSERT #tables SELECT QUOTENAME(TABLE_NAME) FROM ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
Dec 08, 2011 at 09:07 PM Amardeep
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x13

asked: Dec 08, 2011 at 02:08 AM

Seen: 1470 times

Last Updated: Dec 08, 2011 at 02:08 AM