查询数据库表数据大小

时　间:2017-07-23 09:23:18
作　者:缪炜   ID：24010  城市：江阴
摘　要:sql server存储过程显示数据库表中的数据大小
正　文:

Create PROCEDURE [dbo].[spDB_Table_RecordCount]
AS
BEGIN
    DECLARE @tname nvarchar(50)
    DECLARE @count int
    DECLARE @exesql nvarchar(200)
    DECLARE My_Cursor CURSOR FOR (Select name FROM SysObjects where xtype='U')
    OPEN My_Cursor;
    Create TABLE #tempTable(tname nvarchar(50),rcount int);
    FETCH NEXT FROM My_Cursor INTO @tname;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @exesql='Select @_count=COUNT(*) FROM '+@tname
            EXEC sp_executesql @exesql
            ,N'@_count int out'
            ,@_count=@count out
            Insert INTO #tempTable(tname,rcount) values(@tname,@count);
            FETCH NEXT FROM My_Cursor INTO @tname;
        END
    CLOSE My_Cursor;
    DEALLOCATE My_Cursor; 
    Select * FROM #tempTable orDER BY rcount DESC;  
END
 
 
 
 
GO