April 30, 2010 at 8:27 am
I'm look for a script for SS 2005 that will pull the size, preferably of indexes and data, for all tables in a database. I've googled and haven't had much luck. I could get it by hacking up a script and using sp_spaceused, but I'm hoping there's quicker and more elegant way using a DMV?
Thx for any help...
April 30, 2010 at 9:03 am
I had this script around.
sp_msforeachdb @command1 = 'USE [?];
DECLARE @top int
DECLARE @include_system_tables bit
SET @top= 5
SET @include_system_tables = 0
BEGIN
IF @top > 0
SET ROWCOUNT @top
--INSERT INTO master.dbo.GetbiggestTables (DatabaseName,Table_name,Row_Count,TableSpace_MB,TableCreateDate)
SELECT cast(db_name() as varchar(15)) as dbname,cast([Table Name] as varchar(25)) as TableName, (SELECT rows FROM sysindexes s WHERE s.indid < 2 AND s.id = OBJECT_ID(a.[Table Name])) AS [Row_Count], [TableSpace_MB],a.crdate as [TableCreateDate]
FROM
( SELECTQUOTENAME(USER_NAME(o.uid)) + ''.'' + QUOTENAME(OBJECT_NAME(i.id)) AS [Table Name],
CONVERT(numeric(15,2),(((CONVERT(numeric(15,2),SUM(i.reserved)) * (SELECT low FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = ''E'')) / 1024.)/1024.)) AS [TableSpace_MB]
,o.crdate
FROMsysindexes i (NOLOCK)
INNER JOIN
sysobjects o (NOLOCK)
ON
i.id = o.id AND
((@include_system_tables = 1 AND o.type IN (''U'', ''S'')) OR o.type = ''U'') AND
((@include_system_tables = 1)OR (OBJECTPROPERTY(i.id, ''IsMSShipped'') = 0))
WHEREindid IN (0, 1, 255)
GROUP BYQUOTENAME(USER_NAME(o.uid)) + ''.'' + QUOTENAME(OBJECT_NAME(i.id)), o.crdate
) as a
ORDER BY [TableSpace_MB] DESC
SET ROWCOUNT 0
END'
Maninder
www.dbanation.com
April 30, 2010 at 9:05 am
Here is one that I believe I got off of SSC
-- Create the temp table for further querying
CREATE TABLE #temp(
rec_idint IDENTITY (1, 1),
table_namevarchar(128),
nbr_of_rowsint,
data_spacedecimal(15,2),
index_spacedecimal(15,2),
total_sizedecimal(15,2),
percent_of_dbdecimal(15,12),
db_sizedecimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
SELECT left(table_name,50), nbr_of_rows, (data_space / 1024) as data_space_MB, (index_space / 1024) as index_space_MB, (total_size / 1024) as total_size_MB, percent_of_db, (db_size / 1024) as db_space_used_MB
FROM #temp
ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
April 30, 2010 at 9:21 am
Thx guys!
April 30, 2010 at 2:09 pm
Kenneth Langner Jr. (4/30/2010)
Here is one that I believe I got off of SSC-- Create the temp table for further querying
CREATE TABLE #temp(
rec_idint IDENTITY (1, 1),
table_namevarchar(128),
nbr_of_rowsint,
data_spacedecimal(15,2),
index_spacedecimal(15,2),
total_sizedecimal(15,2),
percent_of_dbdecimal(15,12),
db_sizedecimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
SELECT left(table_name,50), nbr_of_rows, (data_space / 1024) as data_space_MB, (index_space / 1024) as index_space_MB, (total_size / 1024) as total_size_MB, percent_of_db, (db_size / 1024) as db_space_used_MB
FROM #temp
ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
I use one pretty much like this. It works well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 3:07 pm
But, when I run script posted by "Kenneth Langner Jr." On adventureworks database I get index_space_mb as [0.0781250] for [Sales].[Individual] table
whereas sp_spaceused returns index_size of [49928 KB] for same table.
Something is wrong here!!!!
April 30, 2010 at 3:12 pm
MANU-J. (4/30/2010)
But, when I run script posted by "Kenneth Langner Jr." On adventureworks database I get index_space_mb as [0.0781250] for [Sales].[Individual] tablewhereas sp_spaceused returns index_size of [49928 KB] for same table.
Something is wrong here!!!!
If memory serves correct, we have seen this before. Have you tried to DBCC UPDATEUSAGE?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 3:22 pm
I ran dbcc updateusage(0) with count_rows under adventureworks database still no help!
Manu
April 30, 2010 at 3:30 pm
Since that query relies on a MS provided system stored proc and sp_spaceused is a system stored proc, I would venture that some stats are messed up somewhere.
Try this query and see if it returns similar numbers to either of those two:
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 3:31 pm
Also try to run an updatestats
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 3:31 pm
Kenneth Langner Jr. (4/30/2010)
Here is one that I believe I got off of SSC-- Create the temp table for further querying
CREATE TABLE #temp(
rec_idint IDENTITY (1, 1),
table_namevarchar(128),
nbr_of_rowsint,
data_spacedecimal(15,2),
index_spacedecimal(15,2),
total_sizedecimal(15,2),
percent_of_dbdecimal(15,12),
db_sizedecimal(15,2))
-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"
-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)
-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100
-- Get the data
SELECT left(table_name,50), nbr_of_rows, (data_space / 1024) as data_space_MB, (index_space / 1024) as index_space_MB, (total_size / 1024) as total_size_MB, percent_of_db, (db_size / 1024) as db_space_used_MB
FROM #temp
ORDER BY total_size DESC
-- Comment out the following line if you want to do further querying
DROP TABLE #temp
Thx for this. I plan on using this as I need all tables within all db's without a cursor, since I am going to nest this within a cursor.
April 30, 2010 at 3:40 pm
Even after updating statistics with fullscan results are different.
Also, script posted at http://vyaskn.tripod.com/code/sp_show_huge_tables.txt doesn't give the sales.individual tables in output list.
April 30, 2010 at 3:47 pm
The difference in size may also be that the sp_spaceused is reporting on clustered indexes where the sp_mstablespace may not be.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 30, 2010 at 4:42 pm
This should give you what you are after.
Script to analyze table space usage
April 30, 2010 at 5:13 pm
Michael Valentine Jones (4/30/2010)
This should give you what you are after.Script to analyze table space usage
Nice script.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply