</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
MSSQL数据库中用户表使用情况
最新推荐文章于 2022-03-28 21:33:51 发布