0

I need to find the No. of rows,size of table,index size,data used,datareserved in each table against all the user created database on server level.Is it possible in a single script or query.or i have to query each database.Write now the script which i have got is this.

//---script i have to run on each database.
use MyDbName;

GO
CREATE TABLE #TableSpaceUsed
(
       Table_name NVARCHAR(255),
       Table_rows INT,
       Reserved_KB VARCHAR(20),
       Data_KB VARCHAR(20),
       Index_Size_KB VARCHAR(20),
       Unused_KB VARCHAR(20)

)


INSERT INTO #TableSpaceUsed
EXEC sp_MSforeachtable 'sp_spaceused ''?'''

SELECT TOP 1 db_Name() As DB_Name,Table_name,Table_Rows,
  CONVERT(INT,SUBSTRING(Index_Size_KB,1,LEN(Index_Size_KB) -2)) as indexSizeKB, 
  CONVERT(INT,SUBSTRING(Data_KB,1,LEN(Data_KB) -2)) as DBSizeUsed_KB, 
  CONVERT(INT,SUBSTRING(Reserved_KB,1,LEN(Reserved_KB) -2)) as DBSizeReserved_KB, 
  CONVERT(INT,SUBSTRING(Unused_KB,1,LEN(Unused_KB) -2)) as unusedKB
  FROM #TableSpaceUsed
  ORDER BY DBSizeUsed_KB DESC

DROP TABLE #TableSpaceUsed
1
  • 2
    Rather than reinvent the wheel, give sp_BlitzIndex a shot: firstresponderkit.org Jan 19, 2017 at 21:54

1 Answer 1

1

I believe one I use provides everything you need?

--Table space
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages)/128 AS TotalSpaceMB, 
    SUM(a.used_pages)/128 AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages))/128 AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    AND p.[rows] > 500000
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name

Note the conditions in the WHERE statement which may be different for you. This is one I obtained from another website I believe, so it isn't my work! I can't find the reference to which website though...

EDIT

OK, this one is better, includes non clustered index space, identifies the schema for the tables and includes decimal points:

WITH idxs AS (
    SELECT
        t.object_id as TableID,
        t.name AS TableName,
        SUM(a.used_pages)/128.0 AS 'Indexsize(MB)'
        --, i.*
    FROM 
        sys.tables t
        INNER JOIN sys.indexes AS i ON t.object_id = i.object_id
        INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
        INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
    WHERE i.[type]=2
    GROUP BY t.object_id, t.name
)
SELECT
    s.name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages)/128.0 AS TotalSpaceMB,
    SUM(a.used_pages)/128.0 AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages))/128.0 AS UnusedSpaceMB,
    idxs.[Indexsize(MB)]
FROM
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
LEFT OUTER JOIN
    idxs ON t.object_id = idxs.TableID
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
    --AND p.[rows] > 500000
GROUP BY 
    s.name, t.Name, p.Rows, idxs.[Indexsize(MB)]
ORDER BY 
    t.Name
8
  • Does that query clearly separate data usage versus index usage? It looks like he asked for that in his question.
    – Joe Obbish
    Jan 20, 2017 at 2:48
  • Oh yeah, maybe not, will see if I can include indexes...
    – blobbles
    Jan 20, 2017 at 2:58
  • Oh, this only does the current database. If you put the above into a stored proc and use a master.sys.sp_MSforeachdb... it should work.
    – blobbles
    Jan 20, 2017 at 3:32
  • Be aware sp_MSforeachdb has "issues"... see this and this
    – Hannah Vernon
    Jan 20, 2017 at 3:35
  • Oh yeah, nice one Max, I had no idea. I better go check a maintenance plan I know...
    – blobbles
    Jan 20, 2017 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.