获取系统表使用情况

DECLARE @TableInfo TABLE ( name VARCHAR(500) , sumrows VARCHAR(11) , reserved VARCHAR(500) , data VARCHAR(500) , index_size VARCHAR(50) , unused VARCHAR(50) , pk VARCHAR(50) )
DECLARE @TableName TABLE ( name VARCHAR(500) )
DECLARE @name VARCHAR(500)
DECLARE @pk VARCHAR(500)
INSERT INTO @TableName ( name ) SELECT o.name FROM sysobjects o , sysindexes i WHERE o.id = i.id AND o.Xtype = ‘U’ AND i.indid < 2 ORDER BY i.rows DESC , o.name
WHILE EXISTS ( SELECT 1 FROM @TableName ) BEGIN SELECT TOP 1 @name = name FROM @TableName DELETE @TableName WHERE name = @name DECLARE @objectid INT SET @objectid = OBJECT_ID(@name) SELECT @pk = COL_NAME(@objectid, colid) FROM sysobjects AS o INNER JOIN sysindexes AS i ON i.name = o.name INNER JOIN sysindexkeys AS k ON k.indid = i.indid WHERE o.xtype = ‘PK’ AND parent_obj = @objectid AND k.id = @objectid INSERT INTO @TableInfo ( name , sumrows , reserved , data , index_size , unused ) EXEC sys.sp_spaceused @name UPDATE @TableInfo SET pk = @pk WHERE name = @name END
SELECT F.name as name, F.reserved as reserved, F.data as data, F.index_size as index_size, RTRIM(F.sumrows) AS sumrows , F.unused as unused, ISNULL(p.tdescription, f.name) AS tdescription , F.pk as pk
FROM @TableInfo F LEFT JOIN ( SELECT name = CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE ‘’ END , tdescription = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, ‘’) ELSE ‘’ END FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID = D.ID AND D.XTYPE = ‘U’ AND D.NAME <> ‘DTPROPERTIES’ LEFT JOIN sys.extended_properties F ON D.ID = F.major_id WHERE a.COLORDER = 1 AND F.minor_id = 0 ) P ON F.name = p.name
ORDER BY CONVERT(FLOAT, f.sumrows) DESC,f.name