CREATE TABLE TABLESPACEINFO --创建结果存储表
(NAMEINFO VARCHAR(50),
ROWSINFO INT,
RESERVED VARCHAR(20),
DATAINFO VARCHAR(20),
INDEX_SIZE VARCHAR(20),
UNUSED VARCHAR(20) )
(NAMEINFO VARCHAR(50),
ROWSINFO INT,
RESERVED VARCHAR(20),
DATAINFO VARCHAR(20),
INDEX_SIZE VARCHAR(20),
UNUSED VARCHAR(20) )
DECLARE MYCURSOR CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'
OPEN MYCURSOR
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U'
OPEN MYCURSOR
DECLARE @NAME VARCHAR(100)
FETCH NEXT FROM MYCURSOR INTO @NAME
WHILE(@@FETCH_STATUS =0)
BEGIN
WHILE(@@FETCH_STATUS =0)
BEGIN
INSERT INTO TABLESPACEINFO EXEC SP_SPACEUSED @NAME
FETCH NEXT FROM MYCURSOR INTO @NAME
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR
DEALLOCATE MYCURSOR
SELECT NAMEINFO AS 表名,
ROWSINFO AS 记录数,
RESERVED AS 表保留的空间,
DATAINFO AS 表数据占用空间,
INDEX_SIZE AS 表索引占用空间,
UNUSED AS 未使用空间
FROM TABLESPACEINFO
ORDER BY CAST(LEFT(LTRIM(RTRIM(RESERVED)) , LEN(LTRIM(RTRIM(RESERVED)))-2) AS INT) DESC
ORDER BY CAST(LEFT(LTRIM(RTRIM(RESERVED)) , LEN(LTRIM(RTRIM(RESERVED)))-2) AS INT) DESC
转载于:https://blog.51cto.com/kanshan/692588