MSSQL 파일그룹 정보 보기 (DBA 관리용 시스템 프로시저)

MSSQL의 파일그룹에 대한 정보를 보는 프로시저이다.

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

-- sp_filegroup0

-- 해당 DB의 파일&파일 그룹 정보를 보여준다.

-- 파라미터가 'all'일시 전체 DB의 파일&파일 그룹 정보를 보여준다.

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

USE master

GO

IF OBJECT_ID('sp_filegroup0') IS NOT NULL

   DROP PROCEDURE sp_filegroup0

GO

CREATE PROC sp_filegroup0    

  @param VARCHAR(5) = NULL

AS

  SET NOCOUNT ON

  IF (@param IS NOT NULL AND @param != 'all')

  BEGIN  

    RAISERROR('''@param'' parameter have to be NULL or ''all''...', 16, 1)      

  

    RETURN(1)  

  END  

  DECLARE @db_name SYSNAME

  DECLARE @cdbname SYSNAME

  DECLARE @sql VARCHAR(1000)

  DECLARE @dblist TABLE

  ( dbname SYSNAME

  )

  CREATE TABLE #result_tbl

  ( dbname SYSNAME

  , groupname VARCHAR(250)

  , filename VARCHAR(250)

  , filepath VARCHAR(250)

  , filesize DEC(15,2)

  )

  IF (@param = 'all')

     INSERT INTO @dblist

     SELECT name

     FROM master..sysdatabases

     WHERE name not in ('master','model','msdb','tempdb','pubs','northwind')

  ELSE

  BEGIN

     SET @db_name = db_name()

     IF NOT EXISTS (SELECT name

                    FROM master..sysdatabases

                    WHERE name = @db_name)

     BEGIN

        RAISERROR('There is no ''%s'' database in this SQL Server...', 16,1,@db_name)

        RETURN(1)

     END

     ELSE

       INSERT INTO @dblist

       VALUES (@db_name)

  END

  DECLARE cur_dblist CURSOR FOR 

      SELECT dbname

      FROM @dblist

  OPEN cur_dblist

  FETCH NEXT FROM cur_dblist

  INTO @cdbname

  WHILE @@FETCH_status = 0

  BEGIN

      SET @sql = ''

      SET @sql = @sql + 'SELECT ''' + @cdbname 

      SET @sql = @sql + ''' AS dbname, isnull(b.groupname, '''

      SET @sql = @sql + 'Transaction Log' + ''') AS groupname, rtrim(a.name) AS filename,'

      SET @sql = @sql + ' rtrim(a.filename) AS filepath, '

      SET @sql = @sql + 'CAST(CAST(a.size AS DEC(15,2))*8/1000. AS DEC(15,2)) AS filesize '

      SET @sql = @sql + 'FROM ' + @cdbname + '.dbo.sysfiles a LEFT OUTER JOIN '

      SET @sql = @sql + @cdbname + '.dbo.sysfilegroups b '

      SET @sql = @sql + 'on a.groupid = b.groupid '

      SET @sql = @sql + 'ORDER BY a.fileid '

  

      INSERT INTO #result_tbl exec(@sql)

  

      FETCH NEXT FROM cur_dblist

      INTO @cdbname

  END

  

  CLOSE cur_dblist

  DEALLOCATE cur_dblist

         

  SELECT dBName, groupname "FileGroup", FileName, FileSize, FilePath

  FROM #result_tbl

  DROP TABLE #result_tbl

  SET NOCOUNT OFF

  RETURN(0) --sp_filegroup0

GO

  

GRANT EXECUTE ON sp_filegroup0 TO PUBLIC

GO