[资料整理]MS SQL SERVER下通过SQL获取每个表的大小

  由于是资料整理,不过多说明,直接上代码:

CREATE PROCEDURE GetAllTableSizes AS  
/*
     Obtains spaced used data for ALL user tables in the database
 */
  DECLARE @TableName VARCHAR(100)
     --For storing values in the cursor
     --Cursor to get the name of all user tables from the sysobjects listing
  DECLARE tableCursor CURSOR FOR
   select [name] from dbo.sysobjects
   where
   OBJECTPROPERTY(id, N'IsUserTable') = 1
  FOR READ ONLY
   --A procedure level temp table to store the results
  CREATE TABLE #TempTable (
      tableName varchar(100),
      numberofRows varchar(100),
      reservedSize varchar(50),
      dataSize varchar(50),
      indexSize varchar(50),
      unusedSize varchar(50)
  )
   --Open the cursor
 OPEN tableCursor
   --Get the first table name from the cursor
  FETCH NEXT FROM tableCursor INTO @TableName
   --Loop until the cursor was not able to fetch
  WHILE (@@Fetch_Status >= 0)
  BEGIN
      --Dump the results of the sp_spaceused query to the temp table
      INSERT  #TempTable
      EXEC sp_spaceused @TableName
      --Get the next table name
      FETCH NEXT FROM tableCursor INTO @TableName
  END
   --Get rid of the cursor
  CLOSE tableCursor
  DEALLOCATE tableCursor
  --Select all records so we can use the reults
  SELECT *  FROM #TempTable
  --Final cleanup!
  DROP TABLE #TempTable

GO

EXEC GetAllTableSizes
GO
Wednesday, March 20, 2013 | 其他技术

文章评论

No comments posted yet.

发表评论

Please add 1 and 5 and type the answer here:

关于博主

  一枚成分复杂的网络IT分子,常年游弋于电子商务,属于互联网行业分类中的杂牌军。当前正在待业中...