MSSQL数据库中用户表使用情况

</pre><pre name="code" class="sql">CREATE PROC p_get_all_spaceused
AS
    --创建临时表存储结果
    CREATE TABLE #result
    (
       TableName VARCHAR(50),
       RowsCount INT,
       Reserved VARCHAR(18),
       Data VARCHAR(18),
       Index_size VARCHAR(18),
       Unused VARCHAR(18)
    )
    DECLARE @sql VARCHAR(500)
    DECLARE @TableName VARCHAR(50)
    --获取数据库中所有用户表
    DECLARE cur_tables CURSOR FOR SELECT name FROM sysobjects WHERE xtype='U'
    OPEN cur_tables
    FETCH NEXT FROM cur_tables INTO @TableName
    WHILE @@fetch_status = 0
    BEGIN
       --将逐个表的情况插入到临时表
       SET @sql = 'INSERT INTO #result '
       SET @sql = @sql + ' EXEC sp_spaceused '''+ @TableName + ''' '
       EXEC (@sql)
       FETCH NEXT FROM cur_tables INTO @TableName
    END
    CLOSE cur_tables
    DEALLOCATE cur_tables
    SELECT * FROM #result
    ORDER BY rowscount DESC
GO
 
EXEC p_get_all_spaceused