写了一段SQL，用来产生SQL Server数据库中Table占用空间的报表，单位为KB！

来源：互联网发布：人工智能指那些技术编辑：程序博客网时间：2024/05/16 08:22

--// STEP 1  //--
IF EXISTS (SELECT name FROM sysobjects WHERE name = '#SysTableSize' AND type = 'U')
   DROP TABLE #SysTableSize

--// STEP 2 //--
CREATE TABLE #SysTableSize
(
  TABLE_NAME  VARCHAR(255), 
  TABLE_ROWS  VARCHAR(255), 
  RESERVED    VARCHAR(255),
  DATA        VARCHAR(255), 
  INDEX_SIZE  VARCHAR(255),
  UNUSED      VARCHAR(255)
)

--// STEP 3 //--
DECLARE @TableName VARCHAR(255)

DECLARE TableListCursor CURSOR 
  LOCAL 
  FAST_FORWARD 
  READ_ONLY
FOR
  SELECT name FROM SYSOBJECTS WHERE xtype='U' ORDER BY name

OPEN TableListCursor

FETCH NEXT FROM TableListCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
  -- PRINT @TableName
  INSERT #SysTableSize (TABLE_NAME, TABLE_ROWS, RESERVED, DATA, INDEX_SIZE, UNUSED) EXEC sp_spaceused @TableName
  FETCH NEXT FROM TableListCursor INTO @TableName  
END

CLOSE TableListCursor
DEALLOCATE TableListCursor

--// STEP 4 //--
SELECT 
  UPPER(TABLE_NAME) AS TABLE_NAME, 
  CAST(REPLACE(RESERVED, 'KB', '') AS INT) AS RESERVED_SIZE,
  CAST(REPLACE(RESERVED, 'KB', '') AS INT) AS RESERVED_SIZE,
  CAST(REPLACE(DATA, 'KB', '') AS INT) AS DATA_SIZE,
  CAST(REPLACE(INDEX_SIZE, 'KB', '') AS INT) AS INDEX_SIZE,
  CAST(REPLACE(UNUSED, 'KB', '') AS INT) AS UNUSED_SIZE 
FROM 
  #SysTableSize 
ORDER BY 
  DATA_SIZE DESC

--// STEP 5 //--
DROP TABLE #SysTableSize