본문 바로가기
Programming/MSSQL

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

by 제타 2018. 4. 11.
반응형


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

-- sp_rowlength0

-- 해당 DB의 Table, View의 Row길이를 출력한다.

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

USE master

GO


IF OBJECT_ID('sp_rowlength0') IS NOT NULL

   DROP PROCEDURE sp_rowlength0

GO


CREATE PROC sp_rowlength0    

AS

  SET NOCOUNT ON


  SELECT object_name(b.id) "Obj_Name", a.xtype, SUM(b.length) "Length"

  FROM sysobjects a INNER JOIN syscolumns b

                    ON a.id = b.id

  WHERE a.xtype IN ('u','v','S')

  AND object_name(b.id) NOT LIKE 'dt_%'

  GROUP BY object_name(b.id), a.xtype

  ORDER BY CASE A.XTYPE WHEN 'U' THEN 1

                        WHEN 'V' THEN 2

                        WHEN 'S' THEN 3

           END


  SET NOCOUNT OFF


  RETURN(0) --sp_rowlength0


GO


GRANT EXECUTE ON sp_rowlength0 TO PUBLIC

GO



반응형