[MSSQL] 테이블 / 컬럼 / Comment 조회

✔ MSSQL 사용하면서 테이블 목록, 컬럼 정보 한번에 추출할 일이 있어서 정리해보는 조회 쿼리들.

 

1. 테이블, 컬럼 목록 추출하기

select A.*, isnull(b.CONSTRAINT_NAME,'') CONSTRAINT_NAME from
(		
    select 			
        --TABLE_CATALOG ,			
		TABLE_SCHEMA  ,	
		TABLE_NAME , 	
        COLUMN_NAME  ,			
		ORDINAL_POSITION  ,	
		DATA_TYPE  , 	
        case when data_type in('nvarchar','geography','varbinary','varchar') then CHARACTER_MAXIMUM_LENGTH			
		     when data_type in( 'bit','int','bigint', 'decimal') then NUMERIC_PRECISION	
			 when data_type in ('date', 'datetime2','datetime') then DATETIME_PRECISION
			 else data_type 
	    end as data_max_length,		
        IS_NULLABLE ,			
        isnull(COLUMN_DEFAULT,'') column_default			       			
    from INFORMATION_SCHEMA.COLUMNS			
) A			
 left outer join 			
( 			
    select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE  			
) B 			
on A.COLUMN_NAME = B.COLUMN_NAME 			
and  A.TABLE_NAME = B.TABLE_NAME			
order by a.TABLE_SCHEMA,A.TABLE_NAME,A.ORDINAL_POSITION;

 

2. 테이블 comment 조회

select NAME, 			
      (select VALUE from SYS.EXTENDED_PROPERTIES where MAJOR_ID = A.ID AND MINOR_ID = 0 ) COMMENT			
  from SYSOBJECTS A			
where  RTRIM(A.XTYPE) = 'U'			
order by NAME;

 

 

3. 컬럼 정보(commen 포함) 조회

select  A.TABLE_NAME, 
		C.VALUE AS TABLE_COMMENT,
		A.COLUMN_NAME, A.DATA_TYPE, 
		ISNULL(CAST(A.CHARACTER_MAXIMUM_LENGTH AS VARCHAR),  
				CAST(A.NUMERIC_PRECISION AS VARCHAR) + ',' +	
				CAST(A.NUMERIC_SCALE AS VARCHAR)) AS COLUMN_LENGTH,
					A.COLUMN_DEFAULT, A.IS_NULLABLE,
					B.VALUE AS COLUM_COMMENT
	from INFORMATION_SCHEMA.COLUMNS A 
	left outer join SYS.EXTENDED_PROPERTIES B
	on B.MAJOR_ID = OBJECT_ID(A.TABLE_NAME) 
	and A.ORDINAL_POSITION = B.MINOR_ID
	left outer join
(select OBJECT_ID(OBJNAME) AS TABLE_ID, VALUE
	from ::FN_LISTEXTENDEDPROPERTY(NULL, 'USER','DBO','TABLE',NULL, NULL, NULL)) C
		on OBJECT_ID(A.TABLE_NAME) = C.TABLE_ID
	--where A.TABLE_NAME = '테이블명'
      order by A.TABLE_NAME, A.ORDINAL_POSITION

SQL Server Management