MSSQL DB 설정 보기 (DBA 관리용 시스템 프로시저)

MSSQL 데이터베이스 설정을 보여주는 프로시저이다.

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

-- sp_dboption0

-- 데이터베이스 설정 옵션을 보여준다.

-- 파라미터가 null인 경우 전체 DB의 옵션을 보여준다.

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

USE MASTER

GO

IF OBJECT_ID('sp_dboption0') IS NOT NULL

   DROP PROCEDURE sp_dboption0

GO

CREATE PROC sp_dboption0

  @dbname SYSNAME = null

AS

  SET NOCOUNT ON

  DECLARE @errmsg VARCHAR(100)

  DECLARE @sql VARCHAR(1000)

  DECLARE @db VARCHAR(100)

  CREATE TABLE #return

  ( dbname SYSNAME NULL

  , options varchar(100) NULL

  )

  IF (@dbname IS NOT NULL) 

  BEGIN

    IF NOT EXISTS (SELECT name 

                   FROM master..sysdatabases

                   WHERE name = @dbname

                  )

    BEGIN

      SET @errmsg = 'There is no ''%s'' Database in this SQL Server...'   

      RAISERROR(@errmsg, 16,1, @dbname)

      RETURN(1)

    END

    ELSE

    BEGIN

      SET @sql = 'sp_dboption ''' + @dbname + ''''

      EXEC (@sql)

    END

  END

  ELSE

  BEGIN -- @dbname is null

    DECLARE cur_dboption CURSOR READ_ONLY

    FOR SELECT name

        FROM master..sysdatabases

    OPEN cur_dboption

    FETCH NEXT FROM cur_dboption

    INTO @db

    WHILE (@@fetch_status = 0)

    BEGIN

      SET @sql = 'sp_dboption ''' + @db + ''''

      INSERT #return (options)

      EXEC (@sql)

      UPDATE #return

      SET dbname = @db

      WHERE dbname IS NULL

      FETCH NEXT FROM cur_dboption

      INTO @db

    END  

    CLOSE cur_dboption

    DEALLOCATE cur_dboption

    SELECT *

    FROM #return

    DROP TABLE #return

  END

  

  RETURN(0) --sp_dboption0

  

  SET NOCOUNT OFF

GO

  

GRANT EXECUTE ON sp_dboption0 TO PUBLIC

GO