统计数据库表信息的脚本

--Get Table usage space

if object_id(N'tempdb..[#TableSizes]') is not null

  drop table #TableSizes ;

 

 

/*—————————————————————————-*/

/* Create the temp table                                                     */

/*—————————————————————————-*/

create table #TableSizes

  (

    [Table Name] nvarchar(128)   /* Name of the table */

  , [Number of Rows] char(11)    /* Number of rows existing in the table. */

  , [Reserved Space] varchar(18) /* Reserved space for table. */

  , [Data Space] varchar(18)    /* Amount of space used by data in table. */

  , [Index Size] varchar(18)    /* Amount of space used by indexes in table. */

  , [Unused Space] varchar(18)   /* Amount of space reserved but not used. */

  ) ;

 

 

/*—————————————————————————-*/

/* Load the temp table                                                        */

/*—————————————————————————-*/

declare @schemaname varchar(256) ;

-- Make sure to set next line to the Schema name you want!

set @schemaname = 'dbo' ;

 

-- Create a cursor to cycle through the names of each table in the schema

declare curSchemaTable cursor

  for select sys.schemas.name + '.' + sys.objects.name

      from    sys.objects

            , sys.schemas

      where   object_id > 100

              and sys.schemas.name = @schemaname

              /* For a specific table uncomment next line and supply name */

            --and sys.objects.name = ’specific-table-name-here’   

              and type_desc = 'USER_TABLE'

              and sys.objects.schema_id = sys.schemas.schema_id ;

 

open curSchemaTable ;

declare @name varchar(256) ;  /* This holds the name of the current table*/

 

-- Now loop thru the cursor, calling the sp_spaceused for each table

fetch curSchemaTable into @name ;

while ( @@FETCH_STATUS = 0 )

  begin    

    insert into #TableSizes

            exec sp_spaceused @objname = @name ;       

    fetch curSchemaTable into @name ;   

  end

 

-- Important to both close and deallocate!

close curSchemaTable ;     

deallocate curSchemaTable ;

 

/*—————————————————————————-*/

/* Feed the results back                                                     */

/*—————————————————————————-*/

select [Table Name] AS TableName

    , [Number of Rows] AS NoOfRows

    , REPLACE([Reserved Space],'KB','') AS [ReservedSpace]

    , REPLACE([Data Space],'KB','') AS [DataSpace]

    , REPLACE([Index Size],'KB','') AS [IndexSize]

    , REPLACE([Unused Space],'KB','') AS [UnusedSpace]

from    [#TableSizes]

order by CAST ([Number of Rows] AS INT) DESC

--order by [Table Name] ;

 

/*—————————————————————————-*/

/* Remove the temp table                                                     */

/*—————————————————————————-*/

drop table #TableSizes ;

转载于:https://www.cnblogs.com/micolour/archive/2010/07/09/1774389.html