建存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 说明:更新查询数据库中各表的大小,结果存储到数据表中
-- =============================================
CREATE PROCEDURE [dbo].[P_GetTableSpaceInfo]
AS
BEGIN
--查询是否存在结果存储表
IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tableSpaceInfo') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--不存在则创建
CREATE TABLE temp_tableSpaceInfo
(name NVARCHAR(128),
rows char(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18))
END
--清空数据表
DELETE FROM temp_tableSpaceInfo
--定义临时变量在遍历时存储表名称
DECLARE @tablename VARCHAR(255)
--使用游标读取数据库内所有表表名
DECLARE table_list_cursor CURSOR FOR
SELECT name FROM sysobjects
WHERE OBJECTPROPERTY(id, N'IsTable') = 1 AND name NOT LIKE N'#%%' ORDER BY name
--打开游标
OPEN table_list_cursor
--读取第一条数据
FETCH NEXT FROM table_list_cursor INTO @tablename
--遍历查询到的表名
WHILE @@FETCH_STATUS = 0
BEGIN
--检查当前表是否为用户表
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename)
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
--当前表则读取其信息插入到表格中
EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo
EXEC sp_spaceused @tbname', N'@tbname varchar(255)', @tbname = @tablename
END
--读取下一条数据
FETCH NEXT FROM table_list_cursor INTO @tablename
END
--释放游标
CLOSE table_list_cursor
DEALLOCATE table_list_cursor
END
GO
查看
EXEC P_GetTableSpaceInfo
SELECT * FROM temp_tableSpaceInfo;
标签:--,tablename,list,每张,cursor,tableSpaceInfo,SQL,table,SERVER
From: https://www.cnblogs.com/vipsoft/p/16798759.html