查询表数据信息

CREATE TABLE #TableInfo
(
name NVARCHAR(50) ,
rows INT ,
reserved NVARCHAR(20) ,
data NVARCHAR(20) ,
index_size NVARCHAR(20) ,
unused NVARCHAR(20)
);

DECLARE @TableName NVARCHAR(255);

DECLARE @CmdSql NVARCHAR(1000);

DECLARE Info_Cursor CURSOR
FOR
SELECT o.name
FROM dbo.sysobjects o
WHERE o.type = 'u';

OPEN Info_Cursor;

FETCH NEXT FROM Info_Cursor 
INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TableInfo
EXEC sp_spaceused @TableName;

FETCH NEXT FROM Info_Cursor INTO @TableName; 
END;

CLOSE Info_Cursor; 
DEALLOCATE Info_Cursor; 
GO

sp_spaceused @UpdateUsage = 'TRUE';

SELECT *
FROM #TableInfo
ORDER BY CAST(LEFT(LTRIM(RTRIM(reserved)), LEN(LTRIM(RTRIM(reserved))) - 2) AS INT) DESC ,
rows DESC;

DROP TABLE #TableInfo;

  

posted @ 2017-12-21 16:35  HUGO.CM  阅读(169)  评论(0编辑  收藏  举报