GaramX

이 문서는 읽기 전용입니다. 원본을 볼 수는 있지만 바꿀 수는 없습니다. 문제가 있다고 생각하면 관리자에게 문의하세요.

====== Table Information ======
<code sql>
CREATE OR ALTER       PROCEDURE [dbo].[P_INFO_TABLE]   
    @table_name     sysname,
    @table_schema   sysname = null
AS  
SELECT CASE WHEN A.ORDINAL_POSITION = 1 THEN A.TABLE_NAME ELSE '' END AS TABLE_NAME,
       CASE WHEN A.ORDINAL_POSITION = 1 THEN C.TABLE_COMMENT ELSE '' END AS TABLE_COMMENT,
       A.COLUMN_NAME,
       B.COLUMN_COMMENT,
	   A.DATA_TYPE,
       ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),
              ISNULL(CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(A.NUMERIC_SCALE AS VARCHAR),
			         '')) AS COLUMN_LENGTH,
	   case when A.IS_NULLABLE = 'YES' THEN 'Y' else 'N' end as IS_NULLABLE,
       case when A.COLUMN_DEFAULT is not null THEN A.COLUMN_DEFAULT ELSE '' END AS COLUMN_DEFAULT,
		   A.COLUMN_NAME + N', -- ' + ISNULL(CONVERT(VARCHAR(max), B.COLUMN_COMMENT),'') AS WOW
  FROM INFORMATION_SCHEMA.COLUMNS A
  LEFT OUTER JOIN (SELECT OBJNAME COLUMN_NAME , VALUE COLUMN_COMMENT
			FROM ::FN_LISTEXTENDEDPROPERTY(NULL, isnull(@table_schema,'user'), 'DBO', 'TABLE', @table_name, 'COLUMN', DEFAULT)
			where NAME = 'MS_Description'
		) B
    ON A.COLUMN_NAME =  B.COLUMN_NAME COLLATE Korean_Wansung_CI_AS
  LEFT OUTER JOIN (
  			SELECT OBJNAME AS TABLE_NAME,
  			       VALUE TABLE_COMMENT
  			  FROM ::FN_LISTEXTENDEDPROPERTY (N'MS_Description', isnull(@table_schema,'user'),'dbo','table', @table_name, NULL, NULL)
       ) C
    ON A.TABLE_NAME = C.TABLE_NAME COLLATE Korean_Wansung_CI_AS
 WHERE A.TABLE_NAME = @table_name
 ORDER BY A.TABLE_NAME, A.ORDINAL_POSITION
</code>

GaramX

사용자 도구

사이트 도구

문서 도구