본문 바로가기
DataBase/MS-SQL

MS-SQL DB Spec 생성하는 프로시져

by 언덕너머에 2015. 2. 21.

CREATE PROC [dbo].[dba_SpaceUsed]
     @SourceDB varchar ( 128 ) = null -- Optional database name
          -- If omitted, the current database is reported.
   , @SortBy char(1) = 'S' -- N for name, S for Size
            -- T for table name
 AS
 
 SET NOCOUNT ON
 
 DECLARE @sql nvarchar (4000)
 
 IF @SourceDB IS NULL BEGIN
     SET @SourceDB = DB_NAME () -- The current DB
 END
 
 --------------------------------------------------------
 -- Create and fill a list of the tables in the database.
 
 CREATE TABLE #Tables (    [schema] sysname
                       , TabName sysname )
 
 SELECT @sql = 'insert #tables ([schema], [TabName])
                   select TABLE_SCHEMA, TABLE_NAME
                   from ['+ @SourceDB +'].INFORMATION_SCHEMA.TABLES
                       where TABLE_TYPE = ''BASE TABLE'''
 EXEC (@sql)
 
 
 ---------------------------------------------------------------
 -- #TabSpaceTxt Holds the results of sp_spaceused.
 -- It Doesn't have Schema Info!
 CREATE TABLE #TabSpaceTxt (
                          TabName sysname
                        , [Rows] varchar (11)
                        , Reserved varchar (18)
                        , Data varchar (18)
                        , Index_Size varchar ( 18 )
                        , Unused varchar ( 18 )
                        )
 
 ---------------------------------------------------------------
 -- The result table, with numeric results and Schema name.
 CREATE TABLE #TabSpace ( [Schema] sysname
                        , TabName sysname
                        , [Rows] bigint
                        , ReservedMB numeric(18,3)
                        , DataMB numeric(18,3)
                        , Index_SizeMB numeric(18,3)
                        , UnusedMB numeric(18,3)
                        )
 
 DECLARE @Tab sysname -- table name
       , @Sch sysname -- owner,schema
 
 DECLARE TableCursor CURSOR FOR
     SELECT [SCHEMA], TabNAME
          FROM #tables
 
 OPEN TableCursor;
 FETCH TableCursor into @Sch, @Tab;
 
 WHILE @@FETCH_STATUS = 0 BEGIN
 
     SELECT @sql = 'exec [' + @SourceDB
        + ']..sp_executesql N''insert #TabSpaceTxt exec sp_spaceused '
        + '''''[' + @Sch + '].[' + @Tab + ']' + '''''''';
 
     Delete from #TabSpaceTxt; -- Stores 1 result at a time
     EXEC (@sql);
 
     INSERT INTO #TabSpace
     SELECT @Sch
          , [TabName]
          , convert(bigint, rows)
          , convert(numeric(18,3), convert(numeric(18,3),
                 left(reserved, len(reserved)-3)) / 1024.0)
                 ReservedMB
          , convert(numeric(18,3), convert(numeric(18,3),
                 left(data, len(data)-3)) / 1024.0) DataMB
          , convert(numeric(18,3), convert(numeric(18,3),
                 left(index_size, len(index_size)-3)) / 1024.0)
                  Index_SizeMB
          , convert(numeric(18,3), convert(numeric(18,3),
                 left(unused, len([Unused])-3)) / 1024.0)
                 [UnusedMB]
         FROM #TabSpaceTxt;
 
     FETCH TableCursor into @Sch, @Tab;
 END;
 
 CLOSE TableCursor;
 DEALLOCATE TableCursor;
 
 -----------------------------------------------------
 -- Caller specifies sort, Default is size
 IF @SortBy = 'N' -- Use Schema then Table Name
     SELECT * FROM #TabSpace
        ORDER BY [Schema] asc, [TabName] asc
 ELSE IF @SortBy = 'T'  -- Table name, then schema
     SELECT * FROM #TabSpace
        ORDER BY [TabName] asc, [Schema] asc
 ELSE  -- S, NULL, or whatever get's the default
     SELECT * FROM #TabSpace
        ORDER BY ReservedMB desc
 ;
 
 DROP TABLE #Tables
 DROP TABLE #TabSpaceTxt
 DROP TABLE #TabSpace