Freigeben über

How to determine the space used by the system and user objects in Microsoft Sql Server 2005

  • Artikel

--Query to find space utilized by System tables

SELECT [System Table Name], (SELECT ROWS FROM SysIndexes S WHERE S.Indid < 2 AND S.ID = OBJECT_ID(A.[System Table Name])) AS [Total Rows], [Total Space Used in MB] FROM

(SELECT QUOTENAME(USER_NAME(so.uid)) + '.' + QUOTENAME(OBJECT_NAME(si.id)) AS [System Table Name],

CONVERT(Numeric(15,2),(((CONVERT(Numeric(15,2),SUM(si.Reserved)) * (SELECT LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total Space Used in MB]

FROM SysIndexes si (NOLOCK) INNER JOIN SysObjects so (NOLOCK)

ON    si.id = so.id AND so.type IN ('S') AND (OBJECTPROPERTY(si.id, 'IsMSShipped') = 1)

WHERE indid IN (0, 1, 255)

GROUP BY QUOTENAME(USER_NAME(so.uid)) + '.' + QUOTENAME(OBJECT_NAME(si.id))

) as a

ORDER BY [Total Space Used in MB] DESC

--Query to find space utilized by User tables

SELECT [System Table Name], (SELECT ROWS FROM SysIndexes S WHERE S.Indid < 2 AND S.ID = OBJECT_ID(A.[System Table Name])) AS [Total Rows], [Total Space Used in MB] FROM

(SELECT QUOTENAME(USER_NAME(so.uid)) + '.' + QUOTENAME(OBJECT_NAME(si.id)) AS [System Table Name],

CONVERT(Numeric(15,2),(((CONVERT(Numeric(15,2),SUM(si.Reserved)) * (SELECT LOW FROM master.dbo.spt_values (NOLOCK) WHERE number = 1 AND type = 'E')) / 1024.)/1024.)) AS [Total Space Used in MB]

FROM SysIndexes si (NOLOCK) INNER JOIN SysObjects so (NOLOCK)

ON    si.id = so.id AND so.type IN ('U')

WHERE indid IN (0, 1, 255)

GROUP BY QUOTENAME(USER_NAME(so.uid)) + '.' + QUOTENAME(OBJECT_NAME(si.id))

) as a

ORDER BY [Total Space Used in MB] DESC

Levi Justus
Technical Lead, Microsoft Sql Server

Comments