MSSQL 테이블, 뷰 보기 (DBA 관리용 시스템 프로시저)

다음은 MSSQL DBA용 프로시저로 유저 테이블, 뷰 등을 볼 수 있는 프로시저이다.

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

-- sp_table0

-- 디폴트로 유저 테이블을, 그외 시스템과 뷰를 보여준다.

-- 파라미터로는 'system','view','all'이 있다.

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

USE master

GO

IF OBJECT_ID('sp_table0') IS NOT NULL

   DROP PROCEDURE sp_table0

GO

CREATE PROC sp_table0    

  @table_type VARCHAR(100) = NULL  

AS

  SET NOCOUNT ON

  DECLARE @owner SYSNAME

  DECLARE @name SYSNAME

  DECLARE @oid INT

  DECLARE @type CHAR(2)     

  DECLARE @pages INT

  DECLARE @dbname SYSNAME

  DECLARE @dbsize DEC(15,0)

  DECLARE @logsize DEC(15)

  DECLARE @bytesperpage DEC(15,0)

  DECLARE @pagesperMB DEC(15,0)

  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

  , rows VARCHAR(100) NULL

  , reserved DEC(15,0) NULL

  , data DEC(15,0) NULL

  , indexp DEC(15,0) NULL

  , unused DEC(15,0) 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

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

        -- #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 "Name", filegroup "Filegroup", rows "Rows"

         , CAST(reserved * d.low / (1000*1024) AS DEC(15,2)) "Reserved(MB)"

         , CAST(data * d.low / (1000*1024) AS DEC(15,2)) "Data(MB)"

         , CAST(indexp * d.low / (1000*1024) AS DEC(15,2)) "Index(MB)"

         , CAST(unused * d.low / (1000*1024) AS DEC(15,2)) "Unused(MB)"

    FROM #return_tbl r, master.dbo.spt_values d

    WHERE d.number = 1

      AND d.type = 'E'

    ORDER BY filegroup, 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 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  

  

  DROP TABLE #return_tbl

  SET NOCOUNT OFF

  RETURN(0) --sp_table0

GO

EXEC sp_MS_marksystemobject 'sp_table0'

GO

GRANT EXECUTE ON sp_table0 TO PUBLIC

GO