/************************************************************************************************
-- sp_table5
-- µðÆúÆ®·Î À¯Àú Å×À̺íÀ», ±×¿Ü ½Ã½ºÅÛ°ú ºä¸¦ º¸¿©ÁØ´Ù.
-- Tempdb¿¡¼­ ½ÇÇà½Ã ±³Âø »óÅ°¡ ¹ß»ýÇÑ´Ù.
-- ÆĶó¹ÌÅͷδ 'system','view','all'ÀÌ ÀÖ´Ù.
************************************************************************************************/
USE master
GO

IF OBJECT_ID('sp_table5') IS NOT NULL
   DROP PROCEDURE sp_table5
GO

CREATE PROC sp_table5    
  @table_type VARCHAR(100) = NULL  
AS
  SET NOCOUNT ON
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  DECLARE @owner SYSNAME
  DECLARE @name SYSNAME
  DECLARE @oid INT
  DECLARE @type CHAR(2)     
  DECLARE @pages INT
  DECLARE @dbname SYSNAME
  DECLARE @dbsize BIGINT
  DECLARE @logsize BIGINT
  DECLARE @bytesperpage BIGINT
  DECLARE @pagesperMB BIGINT
  DECLARE @sql VARCHAR(1000)

  IF (OBJECT_ID('#return_tbl') IS NOT NULL)
  BEGIN
     DROP TABLE #return_tbl
  END

  CREATE TABLE #return_tbl
  ( owner SYSNAME NOT NULL
  , name SYSNAME NOT NULL
  , id INT NOT NULL
  , type VARCHAR(10) NOT NULL
  , filegroup SYSNAME NOT NULL
  , lob_filegroup SYSNAME NULL
  , rows BIGINT NULL
  , reserved BIGINT NULL
  , data BIGINT NULL
  , indexp BIGINT NULL
  , unused BIGINT NULL
  )

  IF @table_type IS NULL  
  BEGIN  
    -- ±âº» Å×À̺í Á¤º¸ --
    INSERT INTO #return_tbl (owner, name, id, type, filegroup)
    SELECT u.name "owner", o.name "name", o.id "id" ,'user' "type", f.groupname "filegroup"
    FROM sysusers u INNER JOIN sysobjects o  
                          ON u.uid = o.uid  
                    INNER JOIN sysindexes i
                          ON o.id = i.id
                    INNER JOIN sysfilegroups f 
                          ON i.groupid = f.groupid
    WHERE xtype  = 'u'  
    AND i.indid < 2
    ORDER BY o.name 

    -- Å×À̺í spaceused °è»ê ½ÃÀÛ
    DECLARE cur_tbl CURSOR 
    FOR SELECT owner, name, id
        FROM #return_tbl

    OPEN cur_tbl

    FETCH NEXT FROM cur_tbl
    INTO @owner, @name, @oid
    
    WHILE (@@fetch_status = 0)
    BEGIN
      /*******************************************************************/
        -- LOB Filegroup ̧̉
        UPDATE #return_tbl
        SET lob_filegroup = (select sf.groupname
                             from sysindexes si inner join sysfilegroups sf
                                                on si.groupid = sf.groupid
                             where si.indid = 255
                             and si.id = @oid
                            )        
        WHERE CURRENT OF cur_tbl
      
        -- #return_tbl.reserved
        UPDATE #return_tbl
        SET reserved = (SELECT sum(reserved)
                        FROM sysindexes
                        WHERE indid in (0,1,255)
                        AND id = @oid
                       )
        WHERE CURRENT OF cur_tbl

        -- #return_tbl.data
        SELECT @pages = SUM(dpages)
        FROM sysindexes
        WHERE indid < 2
        AND id = @oid
        
        SELECT @pages = @pages + ISNULL(SUM(used), 0)
        FROM sysindexes
        WHERE indid = 255
        AND id = @oid
 
        UPDATE #return_tbl
        SET data = @pages
        WHERE CURRENT OF cur_tbl

        -- #return_tbl.index
        UPDATE #return_tbl
        SET indexp = (SELECT SUM(used)
                      FROM sysindexes
                      WHERE indid IN (0,1,255)
                      AND id = @oid
                     ) - data
        WHERE CURRENT OF cur_tbl

        -- #return_tbl.unused
        UPDATE #return_tbl
        SET unused = reserved - (SELECT SUM(used)
                                 FROM sysindexes
                                 WHERE indid IN (0,1,255)
                                 AND id = @oid
                                )
        WHERE CURRENT OF cur_tbl
 
        -- #return_tbl.rows
        UPDATE #return_tbl
        SET rows = (SELECT rows
                    FROM sysindexes
                    WHERE indid < 2
                    AND id = @oid
                   )
        WHERE CURRENT OF cur_tbl
      /*******************************************************************/

      FETCH NEXT FROM cur_tbl
      INTO @owner, @name, @oid
    END

    CLOSE cur_tbl
 
    DEALLOCATE cur_tbl
    
    SELECT --id "Id", 
           owner "OWNER", r.name "TABLE", filegroup "FILEGROUP", '(n/a)' "FILEGROUP_TYPE"
         , lob_filegroup "LOB_FILEGROUP"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,rows),112),N'.00',N'') "ROW_COUNT"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,reserved * d.low / 1024),112),N'.00',N'') "RESERVED_kb"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,data * d.low / 1024),112),N'.00',N'') "DATA_kb"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,indexp * d.low / 1024),112),N'.00',N'') "INDEX_kb"
         , REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,unused * d.low / 1024),112),N'.00',N'') "UNUSED_kb"
    FROM #return_tbl r, master.dbo.spt_values d
    WHERE d.number = 1
      AND d.type = 'E'
    ORDER BY owner, r.name  

  END  
  ELSE IF @table_type = 'system'  
  BEGIN  
    SELECT u.name "owner", o.name "name", 'system' "type"  
    FROM sysusers u INNER JOIN sysobjects o  
                               ON u.uid = o.uid  
    WHERE xtype  = 's'  
    ORDER BY u.name, o.name    
  END  
  ELSE IF @table_type = 'all'  
  BEGIN  
    SELECT u.name "owner", o.name "name",   
           CASE xtype   
                WHEN 'S' THEN 'system'  
                WHEN 'U' THEN 'user'  
                WHEN 'V' THEN 'view'  
           END "type"                  
    FROM sysusers u INNER JOIN sysobjects o  
                               ON u.uid = o.uid  
    WHERE xtype IN ('s', 'u', 'v')  
    ORDER BY xtype    
  END  
  ELSE IF @table_type = 'view'  
  BEGIN  
    SELECT u.name "owner", o.name "name", 'view' "type"  
    FROM sysusers u INNER JOIN sysobjects o  
                               ON u.uid = o.uid  
    WHERE xtype  = 'v'  
    ORDER BY o.name  
  END  
  ELSE  
  BEGIN  
    RAISERROR('''%s'' parameter is not valid...', 16, 1, @table_type)  
  
    RETURN(1)  
  END  


  RETURN(0) --sp_table5
GO

EXEC sp_MS_marksystemobject 'sp_table5'
GO

GRANT EXECUTE ON sp_table5 TO PUBLIC
GO


/************************************************************************************************
-- sp_column5 
-- ÇØ´ç DBÀÇ ¸ðµç Å×À̺í.Ä÷³À» º¸¿©ÁØ´Ù
************************************************************************************************/
USE MASTER
GO

IF OBJECT_ID('sp_column5') IS NOT NULL
   DROP PROCEDURE sp_column5
GO

CREATE PROC sp_column5    
AS  
  SET NOCOUNT ON  
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
    DECLARE @own_tbl_nm SYSNAME
    DECLARE @ind_id SMALLINT
    DECLARE @i SMALLINT
    DECLARE @keys SYSNAME

    DECLARE @pk_keys TABLE
    ( tbl_nm SYSNAME
    , own_nm SYSNAME
    , col_nm SYSNAME
    )

    DECLARE cur_pk_keys CURSOR FAST_FORWARD
    FOR SELECT u.name + '.' + object_name(a.parent_obj) "own_tbl_nm"
            , b.indid "ind_id" 
        FROM sysobjects a 
        INNER JOIN sysindexes b 
            ON a.parent_obj = b.id 
            AND object_name(a.id) = b.name
        INNER JOIN dbo.sysusers u
            ON a.uid = u.uid
        WHERE a.xtype = 'PK'

    OPEN cur_pk_keys

    FETCH NEXT FROM cur_pk_keys
    INTO @own_tbl_nm, @ind_id

    WHILE(@@fetch_status = 0)
    BEGIN
        SET @i = 1

        SET @keys = index_col(@own_tbl_nm, @ind_id, @i)

        WHILE (@keys IS NOT NULL)
        BEGIN

            INSERT INTO @pk_keys 
            VALUES (parsename(@own_tbl_nm,1), parsename(@own_tbl_nm,2), @keys)            

            SET @i = @i + 1
            SET @keys = index_col(@own_tbl_nm, @ind_id, @i)
        END

        FETCH NEXT FROM cur_pk_keys
        INTO @own_tbl_nm, @ind_id
    END

    CLOSE cur_pk_keys
    DEALLOCATE cur_pk_keys

    SELECT u.name "OWNER", a.name "TABLE", b.name "COLUMN"
     , UPPER(
        CASE WHEN c.name IN ('nvarchar', 'nchar') 
                THEN c.name + '(' + CAST(b.length/2 AS VARCHAR(5)) + ')'
            WHEN c.name IN ('varchar', 'char', 'varbinary', 'binary') 
                THEN c.name + '(' + CAST(b.length AS VARCHAR(5)) + ')'
            WHEN c.name IN ('decimal', 'numeric') 
                THEN c.name + '(' + CAST(b.xprec AS VARCHAR(5)) + '.' + CAST(b.scale AS VARCHAR(5)) + ')'
            ELSE c.name
        END) "COLUMN_TYPE"
     , b.length "LENGTH_BT"
     , b.isnullable "IS_NULL"
     , CASE WHEN f.col_nm IS NULL THEN 0
            ELSE 1 
       END "IS_PRIMARYKEY"
     , CASE WHEN e.fkey IS NULL THEN 0
            ELSE 1
       END "IS_FOREIGNKEY"    
     , b.colstat "IS_IDENTITY"
	 , b.iscomputed "IS_COMPUTED"
     , b.colorder "COLUMN_ORDER"
    FROM dbo.sysobjects a
    INNER JOIN dbo.sysusers u
        ON a.uid = u.uid
    INNER JOIN dbo.syscolumns b
        ON a.id = b.id
    INNER JOIN dbo.systypes c
        ON b.xusertype = c.xusertype
    LEFT OUTER JOIN dbo.sysindexkeys d
        ON b.id = d.id
        AND b.colid = d.colid
        AND d.indid = 1
    LEFT OUTER JOIN dbo.sysforeignkeys e
        ON b.id = e.fkeyid
        AND b.colid = e.fkey
    LEFT OUTER JOIN @pk_keys f
        ON a.name = f.tbl_nm
        AND u.name = f.own_nm
        AND b.name = f.col_nm
    WHERE a.xtype = 'U'
    ORDER BY u.name, a.name, b.colorder
    
    RETURN(0) --sp_column5
GO

GRANT EXECUTE ON sp_column5 TO PUBLIC
GO


/************************************************************************************************
-- sp_filegroup5
-- ÇØ´ç DBÀÇ ÆÄÀÏ&ÆÄÀÏ ±×·ì Á¤º¸¸¦ º¸¿©ÁØ´Ù.
-- ÆĶó¹ÌÅÍ°¡ 'all' ÀÏ ¶§ Àüü »ç¿ëÀÚ DBÀÇ ÆÄÀÏ&ÆÄÀÏ ±×·ì Á¤º¸¸¦ º¸¿©ÁØ´Ù.
************************************************************************************************/
USE master
GO

IF OBJECT_ID('dbo.sp_filegroup5') IS NOT NULL
    DROP PROC sp_filegroup5
GO

  
CREATE PROC sp_filegroup5      
    @param NCHAR(3) = NULL  
AS  
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  
IF (@param IS NOT NULL AND @param != 'all')  
BEGIN    
RAISERROR('''@param'' parameter have to be NULL or ''all'' only.', 16, 1)  
  
RETURN (1)    
END    
  
DECLARE @db_name SYSNAME  
DECLARE @db_nm SYSNAME  
DECLARE @sql VARCHAR(2000)  
  
DECLARE @dblist TABLE  
( dbname SYSNAME  
)  
  
CREATE TABLE #result  
( [database] SYSNAME  
, [filegroup] SYSNAME  
, logical_file SYSNAME  
, physical_file SYSNAME  
, file_type VARCHAR(60)  
, is_readonly BIT  
, is_default BIT  
, [status] VARCHAR(60)  
, file_size_mb DECIMAL(15,2)  
, free_space_mb DECIMAL(15,2)  
, free_space_pct VARCHAR(10)  
, max_size VARCHAR(30)  
, growth VARCHAR(20)  
, fileid int
)  
  
IF (@param = 'all')  
    INSERT INTO @dblist  
    SELECT name  
    FROM master.dbo.sysdatabases  
    WHERE name not in ('master', 'model', 'msdb', 'tempdb')  
ELSE  
BEGIN  
    SET @db_name = db_name()  
  
    INSERT INTO @dblist  
    VALUES (@db_name)  
END  
  
DECLARE cur_filegroup CURSOR FAST_FORWARD  
FOR SELECT dbname  
    FROM @dblist  
  
OPEN cur_filegroup  
  
FETCH NEXT FROM cur_filegroup  
INTO @db_nm  
  
WHILE (@@FETCH_status = 0)  
BEGIN  
    SET @sql = ''  
    set @sql = @sql      
             + 'use ' + @db_nm + ' '      
             + 'SELECT ''' + @db_nm + ''' "DATABASE"'  
             + '     , ISNULL(fg.groupname, ''TransactiON Log'') "fg_nm" '  
             + '     , RTRIM(f.name) "lgc_f_nm" '  
             + '     , f.filename "phys_f_nm" '  
             + '     , CASE f.groupid WHEN 0 THEN ''LOG'' '  
             + '                      ELSE ''DATA'' '  
             + '       END "FILE_TYPE" '  
             + '     , CASE WHEN fg.status IN (8, 24) THEN 1 '  
             + '            ELSE 0 '  
             + '       END "IS_READONLY" '  
             + '     , CASE WHEN fg.status IN (16, 24) THEN 1 '  
             + '            ELSE 0 '  
             + '       END "IS_DEFAULT" '  
             + '     , CONVERT(VARCHAR(60),DATABASEPROPERTYEX(db_name(), ''Status'')) "STATUS" '  
             + '     , CONVERT(DEC(15,2), f.size*8/1000.0) "FILE_SIZE_mb" '  
             + '     , CONVERT(DEC(15,2), f.size*8/1000.0 - FILEPROPERTY(f.name, ''SpaceUsed'')*8/1000.0) "FREE_SPACE_mb" '  
             + '     , CONVERT(VARCHAR(8) '  
             + '              , CONVERT( DEC(5,2) '  
             + '                         , ( f.size*8/1000.0 - FILEPROPERTY(f.name, N''SpaceUsed'')*8/1000.0 '  
             + '                           ) / (f.size*8/1000.0) * 100 '  
             + '                         ) '  
             + '              ) + ''%'' "FREE_SPACE_pct" '  
             
             
             + '     , case f.maxsize when -1 then N''Unlimited''    
                           else REPLACE(CONVERT(VARCHAR(50),CONVERT(MONEY,CONVERT(BIGINT,f.maxsize) * 8),112),''.00'','''')  + '' KB''   
                       end "MAX_SIZE" '                        
             + '     , case f.status & 0x100000 when 0x100000   
                           then REPLACE(CONVERT(VARCHAR(15),CONVERT(MONEY,f.growth),112),''.00'','''')  + ''%''    
                           else REPLACE(CONVERT(VARCHAR(15),CONVERT(MONEY,f.growth * 8),112),''.00'','''')  + '' KB''    
                       end "GROWTH" '  
             + '     , fileid '
             + 'FROM dbo.sysfiles f '  
             + 'LEFT OUTER JOIN dbo.sysfilegroups fg '  
             + 'ON f.groupid = fg.groupid '  

    INSERT INTO #result exec(@sql)  
  
    FETCH NEXT FROM cur_filegroup  
    INTO @db_nm  
END  
  
CLOSE cur_filegroup  
DEALLOCATE cur_filegroup  

select [DATABASE], filegroup "FileSpace", fileid "File#", Logical_File, Physical_File, File_Type "TYPE"
     , case when is_default = 1 then 'DEFAULT'
            else ''
       end "Default"
     , Status
     , case when [database] = 'ORACLE' then Max_Size + ' MB'
            else Max_Size
       end "Max_Size"
     , cast(file_size_mb as varchar(20)) "File_size(MB)"
     , cast(free_space_mb as varchar(20)) + isnull(' (' + cast(free_space_pct as varchar(20)) + ')', '') "Free_Space(MB)"
     , replace(Growth, 'kb', 'KB') "Growth"
from
(  
    SELECT *  
    FROM #result  
) aa
order by [database]
       , case when filegroup = 'TEMP' then 99
              when filegroup = 'Transaction Log' then 88
              else 11
         end
       , fileid; 
  
RETURN (0) --   sp_filegroup5 'all'  
GO

GRANT EXECUTE ON sp_filegroup5 TO PUBLIC
GO