MSSQL OJBECT 보기 (DBA 관리용 시스템 프로시저)

/************************************************************************************************

-- sp_object0

-- 파라미터 없이 모든 오브젝트를 표시하고 특정 파라미터 기입시 상세 설명이 출력된다.

-- 'help' 파라미터시 파라미터 help manual이 출력된다.

************************************************************************************************/

USE MASTER

GO

IF OBJECT_ID('sp_object0') IS NOT NULL

   DROP PROCEDURE sp_object0

GO

CREATE PROC sp_object0 

  @param VARCHAR(100) = NULL

AS

  SET NOCOUNT ON

  DECLARE @str VARCHAR(2000)

        , @fk_name SYSNAME

        , @fk_table SYSNAME

        , @rk_table SYSNAME

        , @fkey VARCHAR(100)

        , @rkey VARCHAR(100)

        , @fkeys VARCHAR(100)

        , @rkeys VARCHAR(100)

  SET @fkey = ''

  SET @rkey = ''

  SET @fkeys = ''

  SET @rkeys = ''

  CREATE TABLE #temp_tbl  

  ( tblname VARCHAR(255)  

  , idxname VARCHAR(255)  

  , idxdesc VARCHAR(1000)  

  , idxcol VARCHAR(1000)  

  ) 

  CREATE TABLE #fk_tbl

  ( fk_name SYSNAME

  , foreign_table VARCHAR(100)

  , foreign_column VARCHAR(100)

  , delete_action VARCHAR(10)

  , update_action VARCHAR(10)

  , reference_table VARCHAR(100)

  , reference_column VARCHAR(100)

  )

  IF (@param IS NOT NULL) AND 

     (@param NOT IN ('S','U','V','PK','UQ','FK','SP','SF','IF'

                    ,'TF','TR','XP','L', 'help','CK','DF'))

  BEGIN

     RAISERROR('''%s'' is not a designated parameter. 

               Please use ''help'' parameter.', 16, 1,@param)

    RETURN(1)

  END

  IF (@param IS NULL)

  BEGIN

    SELECT "ObjectType" = 

      CASE xtype WHEN 'F'  THEN 'FOREIGN KEY'

                 WHEN 'L'  THEN 'LOG'

                 WHEN 'FN' THEN 'SCHOLAR FUNCTION'

                 WHEN 'IF' THEN 'INLINE TABLE FUNCTION'

                 WHEN 'P'  THEN 'USER PROCEDURE'

                 WHEN 'PK' THEN 'PRIMARY KEY'

                 WHEN 'S'  THEN 'SYSTEM TABLE'

                 WHEN 'TF' THEN 'TABLE FUNTION'

                 WHEN 'TR' THEN 'TRIGGER'

                 WHEN 'U'  THEN 'USER TABLE'

                 WHEN 'UQ' THEN 'UNIQUE'

                 WHEN 'D'  THEN 'Default'

                 WHEN 'C'  THEN 'Cehck'

                 WHEN 'V'  THEN 'VIEW'

                 WHEN 'X'  THEN 'EXTENDED PROCEDURE'

      END

    , OBJECT_NAME(parent_obj) "ParentObject"

    , name "ObjectName"

    FROM sysobjects

    WHERE xtype != 'S'

    ORDER BY CASE xtype WHEN 'S'  THEN 1

                        WHEN 'U'  THEN 2

                        WHEN 'V'  THEN 3

                        WHEN 'PK' THEN 4

                        WHEN 'UQ' THEN 5

                        WHEN 'D'  THEN 6

                        WHEN 'C'  THEN 7

                        WHEN 'F'  THEN 8

                        WHEN 'P'  THEN 9

                        WHEN 'FN' THEN 10

                        WHEN 'IF' THEN 11

                        WHEN 'TF' THEN 12

                        WHEN 'TR' THEN 13

                        WHEN 'X'  THEN 14

                        WHEN 'L'  THEN 15

                        ELSE 16

             END

    RETURN(0)

  END

  IF (@param = 'help')

  BEGIN

    PRINT ' SUBJECT'

    PRINT '     This procedure is for viewing all objects in selected database. '

    PRINT ' '

    PRINT ' DESCRIPTION'

    PRINT '     EXEC sp_object0'

    PRINT '         View all objects in selected database.'

    PRINT ' '

    PRINT '     EXEC sp_object0 ''help'''

    PRINT '         View help manual with usage, parameter.'

    PRINT ' '

    PRINT '     EXEC sp_object0 ''<parameter>'''

    PRINT '         View specified object by a parameter.'

    PRINT ' ' 

    PRINT ' PARAMETER'

    PRINT '     S  : System Table'

    PRINT '     U  : User Table'

    PRINT '     V  : View'

    PRINT '     PK : Primary Key'

    PRINT '     UQ : Unique'

    PRINT '     FK : Foreign Key'

    PRINT '     SP : Stored Procedure'

    PRINT '     XP : Extened Procedure'

    PRINT '     SF : Scholar Function'

    PRINT '     IF : Inline Table Function'

    PRINT '     TF : Table Function'

    PRINT '     TR : Trigger'

    PRINT '     CK : Check'

    PRINT '     DF : Default'

    PRINT '     L  : Log'

    PRINT ' '

    PRINT ' END'

 

    RETURN(0)

  END

  IF (@param in ('S','U','V'))

  BEGIN

    SELECT name "ObjectName"

    FROM sysobjects

    WHERE xtype = @param

    AND status >= CASE WHEN @param ='S' THEN -2147483648

                       WHEN @param IN ('U','V') THEN 0

                  END

    RETURN(0)

  END

 

  IF (@param in ('PK', 'UQ'))

  BEGIN

    INSERT INTO #temp_tbl EXEC('sp_index0')

    SELECT OBJECT_NAME(s.parent_obj) "TableName", s.name "Name", t.idxcol "ColumnName"

    FROM sysobjects s INNER JOIN #temp_tbl t

                      ON OBJECT_NAME(s.parent_obj) = t.tblname

    WHERE s.xtype = @param

    AND t.idxdesc LIKE CASE @param WHEN 'PK' THEN '%primary key%'

                                   WHEN 'UQ' THEN '%unique key%'

                       END

    RETURN(0)

  END

  IF (@param in ('FK'))

  BEGIN

    INSERT INTO #fk_tbl(fk_name, foreign_table, reference_table, delete_action, update_action)

    SELECT o.name, object_name(o.parent_obj), object_name(r.rkeyid)

         , CASE ObjectProperty(r.constid, 'CnstIsDeleteCascade') WHEN 1 THEN 'Cascade'

                                                                 WHEN 0 THEN 'No Action'

           END  

         , CASE ObjectProperty(r.constid, 'CnstIsUpdateCascade') WHEN 1 THEN 'Cascase'

                                                                 WHEN 0 THEN 'No Action'

           END

    FROM sysobjects o INNER JOIN sysreferences r

                      ON o.id = r.constid

    WHERE xtype = 'F'

    

    DECLARE cur_fk_col CURSOR 

    FOR SELECT fk_name, foreign_table, reference_table

        FROM #fk_tbl

    OPEN cur_fk_col

    FETCH NEXT FROM cur_fk_col

    INTO @fk_name, @fk_table, @rk_table

    WHILE (@@fetch_status = 0)

    BEGIN

         SET @rkeys = ''

         SET @fkeys = ''

         DECLARE cur_fk_col2 CURSOR 

         FOR SELECT rkey, fkey

         FROM sysforeignkeys

         WHERE constid = object_id(@fk_name)

         OPEN cur_fk_col2   

         FETCH NEXT FROM cur_fk_col2

         INTO @rkey, @fkey  

         WHILE (@@fetch_status = 0)

         BEGIN

              SET @rkeys = @rkeys + col_name(object_id(@rk_table),@rkey) + ', '

              SET @fkeys = @fkeys + col_name(object_id(@fk_table),@fkey) + ', '

              FETCH NEXT FROM cur_fk_col2

              INTO @rkey, @fkey 

         END

         CLOSE cur_fk_col2

 

         DEALLOCATE cur_fk_col2

         UPDATE #fk_tbl

         SET foreign_column = '(' + SUBSTRING(@fkeys,1, LEN(@fkeys)-1) + ')'

           , reference_column = '(' + SUBSTRING(@rkeys,1, LEN(@rkeys)-1) + ')'

         WHERE CURRENT OF cur_fk_col

         FETCH NEXT FROM cur_fk_col

         INTO @fk_name, @fk_table, @rk_table

    END

    CLOSE cur_fk_col

 

    DEALLOCATE cur_fk_col

    SELECT * FROM #fk_tbl

    RETURN(0)

  END

  IF (@param in ('SP','SF','IF','TF','XP'))

  BEGIN

    SELECT name "ObjectName"

    FROM sysobjects

    WHERE xtype = CASE @param WHEN 'SP' THEN 'P'

                              WHEN 'SF' THEN 'FN'

                              WHEN 'XP' THEN 'X'

                              ELSE @param

                  END

    AND status >= CASE WHEN @param ='XP' THEN -2147483648

                       WHEN @param IN ('SP','SF','IF','TF') THEN 0

                  END

    RETURN(0)

  END

  IF (@param in ('L'))

  BEGIN

    PRINT 'There is no finish scripting with Log...'    

    RETURN(0)

  END

  IF (@param = 'TR')

  BEGIN

    SELECT name "TRG_NAME"  

         , user_name(uid) "TRG_OWNER"  

         , ObjectProperty( id, 'ExecIsUpdateTrigger') "IS_UPDATE"  

         , ObjectProperty( id, 'ExecIsDeleteTrigger') "IS_DELETE"  

         , ObjectProperty( id, 'ExecIsInsertTrigger') "IS_INSERT"  

         , ObjectProperty( id, 'ExecIsAfterTrigger') "AFTER_TRG"  

         , ObjectProperty( id, 'ExecIsInsteadOfTrigger') "INSEAD_OF_TRG"  

    FROM sysobjects  

    WHERE type = @param

    RETURN(0)

  END

  IF (@param in ('DF','CK'))

  BEGIN

     SELECT so.name "Name"

          , OBJECT_NAME(so.parent_obj) "TableName"

          , cl.name "ColumnName"

          , cm.text "Contents"

     FROM sysobjects so INNER JOIN sysconstraints cs

                        ON so.id = cs.constid

                        INNER JOIN syscomments cm

                        ON so.id = cm.id

                        INNER JOIN syscolumns cl

                        ON so.parent_obj = cl.id

                        AND cs.colid = cl.colid

     WHERE so.xtype = CASE @param WHEN 'DF' THEN 'D'

                                  WHEN 'CK' THEN 'C'

                      END

     AND cm.colid = 1 

    RETURN(0)

  END

  DROP TABLE #temp_tbl

  DROP TABLE #fk_tbl

  RETURN(0) -- sp_object0 

  SET NOCOUNT OFF

GO

EXEC sp_MS_marksystemobject 'sp_object0'

GO

  

GRANT EXECUTE ON sp_object0 TO PUBLIC

GO