查询数据库中所有表占用空间的语句

SQL code

  
  

   
   IF
   
    
   
   OBJECT_ID
   
   (
   
   '
   
   tempdb..#TB_TEMP_SPACE
   
   '
   
   ) 
   
   IS
   
    
   
   NOT
   
    
   
   NULL
   
    
   
   DROP
   
    
   
   TABLE
   
    #TB_TEMP_SPACE

   
   GO
   
   

   
   CREATE
   
    
   
   TABLE
   
    #TB_TEMP_SPACE(
NAME 
   
   VARCHAR
   
   (
   
   500
   
   )
,ROWS 
   
   INT
   
   
,RESERVED 
   
   VARCHAR
   
   (
   
   50
   
   )
,DATA 
   
   VARCHAR
   
   (
   
   50
   
   )
,INDEX_SIZE 
   
   VARCHAR
   
   (
   
   50
   
   )
,UNUSED 
   
   VARCHAR
   
   (
   
   50
   
   )
)

   
   GO
   
   
SP_MSFOREACHTABLE 
   
   '
   
   INSERT INTO #TB_TEMP_SPACE exec sp_spaceused 
   
   ''
   
   ?
   
   '''
   
   

   
   GO
   
   

   
   SELECT
   
    
   
   *
   
   

   
   FROM
   
    #TB_TEMP_SPACE

   
   ORDER
   
    
   
   BY
   
    
   
   REPLACE
   
   (DATA,
   
   '
   
   KB
   
   '
   
   ,
   
   ''
   
   )
   
   +
   
   0
   
    
   
   DESC
   
   

   
   GO