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
(comments are locked)
|
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. Thank you again.:)
Dec 08, 2011 at 03:40 AM
OnlyGraphite
(comments are locked)
|
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)
|
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)
FETCH NEXT FROM CUR_SR_SPACE INTO @SourceDB END CLOSE CUR_SR_SPACE DEALLOCATE CUR_SR_SPACE SELECT * FROM #ServerSpaceUsed DROP TABLE #ServerSpaceUsed 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
Dec 08, 2011 at 04:26 AM
Sacred Jewel
(comments are locked)
|
(comments are locked)
|
Please can someone give more inputs on SQL Server meta data?
Thanks in advance.
Cheers - DV