MSSQL 제약사항 보기 (DBA 관리용 시스템 프로시저)

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

-- sp_constraint0

-- 해당 DB 전체의 제약사항이나 특정 오브젝트의 제약사항을 출력한다.

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

USE MASTER

GO

IF OBJECT_ID('sp_constraint0') IS NOT NULL

   DROP PROCEDURE sp_constraint0

GO

CREATE PROC sp_constraint0 

  @objname VARCHAR(100) = NULL

AS

  SET NOCOUNT ON

  DECLARE @dbname SYSNAME

  DECLARE @sql VARCHAR(1000)

  

  SET @dbname = db_name()

  SET @sql = ''

  IF (@objname IS NULL)

  BEGIN

    SET @sql = 'SELECT object_name(id) "OBEJCT_NAME", object_name(constid) "CONST_NAME" '

    SET @sql = @sql + 'FROM ' + @dbname + '..sysconstraints '

    SET @sql = @sql + 'ORDER BY object_name(id)'

    EXEC (@sql)

  END

  ELSE

  BEGIN

    IF NOT EXISTS(SELECT ID

                  FROM sysobjects

                  WHERE id = object_id(@objname)

                    AND xtype IN ('S','U', 'V')

                 )

    BEGIN

       RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)

       RETURN(1)

    END

    SET @sql = 'SELECT object_name(id) "OBEJCT_NAME", object_name(constid) "CONST_NAME" '

    SET @sql = @sql + 'FROM ' + @dbname + '..sysconstraints WHERE id = '

    SET @sql = @sql + CAST(object_id(@objname) as VARCHAR(10))

    SET @sql = @sql + 'ORDER BY object_name(id)'

    

    EXEC (@sql)

  END

  RETURN(0) --sp_constraint0

  SET NOCOUNT OFF

GO

  

GRANT EXECUTE ON sp_constraint0 TO PUBLIC

GO