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

avatar image

OnlyGraphite
41 6 6 10

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

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

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

avatar image

ThomasRushton ♦♦
40.3k 20 49 53

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

avatar image

Sharma
1.3k 88 91 95

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 Sharma
(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.

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:

x15

asked: Dec 08, 2011 at 02:08 AM

Seen: 1734 times

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

Copyright 2016 Redgate Software. Privacy Policy