June 5, 2012 at 9:01 am
Thanks,
Bubby
June 5, 2012 at 9:05 am
Following will give you table/index with occupied data pages. Each datapage has 8k size.
select OBJECT_NAME(id) TableName,name,dpages,reserved,used from sys.sysindexes
June 5, 2012 at 9:11 am
The following link will explain the data this gives you.
http://msdn.microsoft.com/en-us/library/ms188776.aspx
It uses a combo of sp_msForEachTable and sp_spaceused to gather the data.
Enjoy.
SET NOCOUNT ON
CREATE TABLE #T
(
table_name NVARCHAR(128)
,[rows] CHAR(11)
,reserved VARCHAR(18)
,data VARCHAR(18)
,index_size VARCHAR(18)
,unused VARCHAR(18)
)
CREATE TABLE #U
(
[db_name] varchar(128)
,table_name nvarchar(128)
,[rows] CHAR(11)
,reserved VARCHAR(18)
,data VARCHAR(18)
,index_size VARCHAR(18)
,unused VARCHAR(18)
)
DECLARE @command VARCHAR(4000)
SELECT @command =
'
IF ''@'' <> ''master'' AND ''@'' <> ''model'' AND ''@'' <> ''msdb'' AND ''@'' <> ''tempdb''
BEGIN
USE [@] INSERT #T EXEC sp_msForEachTable ''EXEC sp_spaceused ''''?''''''
END
INSERT #U SELECT ''@'', * FROM #T
'
EXEC sp_MSForEachDB @command, '@'
SELECT * FROM #U
DROP TABLE tempdb..#T
DROP TABLE tempdb..#U
June 5, 2012 at 9:11 am
sysindexes is deprecated, will be removed in a future version, there only for backward compat with SQL 2000 and should not be used. It can also be inaccurate about space usage.
Query sys.partitions and sys.allocation_units or sys.dm_db_index_physical_stats.
From Books Online:
SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
JOIN sys.objects AS o ON p.object_id = o.object_id
JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
ORDER BY o.name, p.index_id;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 5, 2012 at 9:19 am
thanks Gail bringing this to attention
June 5, 2012 at 9:23 am
Thanks for your help
June 5, 2012 at 10:43 am
Here's my version (everybody's got their own version :-)). It provides MB totals for each table, each table and all its indexes, total number of indexes per table and whether the table has a clus index or not.
SELECT
s.name AS Schema_Name,
o.name AS Table_Name,
CEILING(SUM(dps.reserved_page_count) / 128.0) AS Table_Plus_Indexes_MB,
CEILING(SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.reserved_page_count ELSE 0 END) / 128.0) AS Table_MB,
SUM(CASE WHEN dps.index_id IN (0, 1) THEN row_count ELSE 0 END) AS Total_Rows_Table,
SUM(row_count) AS Total_Rows_Table_Plus_Indexes,
COUNT(DISTINCT CASE WHEN dps.index_id > 0 THEN dps.index_id END) AS Total_#_Of_Indexes,
MAX(CASE WHEN dps.index_id = 1 THEN 'Yes' ELSE 'No' END) AS [Has_Clus_Index],
o.create_date
FROM sys.dm_db_partition_stats dps WITH (NOLOCK)
INNER JOIN sys.objects o WITH (NOLOCK) ON
o.object_id = dps.object_id
INNER JOIN sys.schemas s WITH (NOLOCK) ON
s.schema_id = o.schema_id
WHERE
o.type = 'U' AND --user tables only
o.name NOT IN (N'dtproperties') AND
o.name NOT LIKE 'sys%'
--AND o.name NOT LIKE 'MS%'
--AND o.name IN (N'<tablename1>', N'<tablename2>, ...) --if you just want to look at specific table(s)
GROUP BY
s.name, o.name, o.create_date
ORDER BY
CEILING(SUM(dps.reserved_page_count) / 128.0) DESC --from largest to smallest total table plus indexes size
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 5, 2012 at 10:51 am
bubby (6/5/2012)
Thanks,Bubby
Not trying to be a jerk, but in the future please restate the subject or question in the post instead of leaving it to the subject?
Jared
CE - Microsoft
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply