[MS-SQL] 테이블-컬럼 정보 조회

 

특정 테이블에 컬럼정보를 모두 조회해야하는 경우가 있는데, 물론 디자인을 통해서 살펴볼 수도 있겠지만 테이블명, 컬럼명, 컬럼타입, 기재해둔 코멘트, 해당 컬럼에 설정된 디폴트 데이터값 등을 한번에 확인해야할 경우가 있다. 

이럴 땐 아무래도 디자인을 통해서 보는 것보다는 쿼리를 이용하여 전체를 한 눈에 확인하는 게 편하기 때문에 오늘은 그 방법을 확인하고자 한다.

Table 정보조회

DECLARE @table_name VARCHAR(30) = '테이블명'

BEGIN --Table Definiti
  SELECT A.Object_ID AS Object_ID,
			SCHEMA_NAME(SCHEMA_ID) + '.' + Lower(A.NAME) AS TABLE_NAME,
			A.NAME AS TB_NAME,
    		CASE A.TYPE WHEN 'U' THEN 'TABLE'
							WHEN 'V' THEN 'VIEW'
							WHEN 'TF' THEN 'FUNCTION'
							WHEN 'FN' THEN 'FUNCTION'
							WHEN 'IF' THEN 'FUNCTION'
							WHEN 'P' THEN 'PROCEDURE'
							ELSE A.TYPE
			END AS TABTYPE,
			REPLACE(REPLACE(REPLACE(Convert(Varchar(MAX), ISNULL(B.VALUE,'')), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS COMMENTS,
			A.TYPE AS OBJECT_TYPE,
			A.TYPE_DESC AS OBJECT_TYPE_DESC,
			(SELECT COUNT(0)
			   FROM sys.objects AS t WITH(NOLOCK)
						INNER JOIN sys.triggers AS t_detail WITH(NOLOCK) ON t.object_id = t_detail.object_id AND t_detail.is_disabled = 0
			  WHERE t.type IN ('TR', 'TA')
			    AND t.parent_object_id = A.object_id) AS using_trigger_cnt
    FROM SYS.OBJECTS AS A WITH (NOLOCK) LEFT JOIN SYS.EXTENDED_PROPERTIES AS B WITH (NOLOCK) ON A.OBJECT_ID = B.MAJOR_ID AND B.MINOR_ID = 0 AND B.NAME = 'MS_DESCRIPTION'
   WHERE A.TYPE NOT IN ('UQ', 'SQ', 'D', 'PK', 'S', 'IT', 'TR')
     AND Upper(A.NAME) NOT LIKE 'PBC%'
     AND SCHEMA_NAME(A.SCHEMA_ID) NOT IN ('cdc')
     AND A.is_ms_shipped <> 1
	 AND A.name = @table_name
   ORDER BY CASE WHEN A.TYPE = 'U' THEN 1 ELSE 99 END, A.TYPE, A.NAME 
END;

 

테이블 내역 조회

 

위와 같이 '테이블명' 에 필요한 테이블명을 기재하여 조회하면 위와 같이 조회되는 것을 확인할 수 있다.

 

column 정보조회

DECLARE @table_name VARCHAR(30) = '테이블명'

BEGIN -- Column Definition
  SELECT	B.Column_ID, 
			A.NAME AS TABLE_NAME, 
			B.NAME AS COLUMN_NAME,
			UPPER(CONVERT(VARCHAR,TYPE_NAME(B.SYSTEM_TYPE_ID))) +
			(CASE UPPER(CONVERT(VARCHAR, TYPE_NAME(B.SYSTEM_TYPE_ID))) WHEN 'BIGINT' THEN ''
					WHEN 'BIT' THEN ''
					WHEN 'DATE' THEN ''
					WHEN 'DATETIME' THEN ''
					WHEN 'DECIMAL' THEN '(' + CONVERT(VARCHAR, B.PRECISION) + ', ' + CONVERT(VARCHAR, B.SCALE) + ')'
					WHEN 'FLOAT' THEN ''
					WHEN 'GEOGRAPHY' THEN ''
					WHEN 'GEOMETRY' THEN ''
					WHEN 'HIERARCHYID' THEN ''
					WHEN 'IMAGE' THEN ''
					WHEN 'INT' THEN ''
					WHEN 'MONEY' THEN ''
					WHEN 'NCHAR' THEN '(' + CONVERT(VARCHAR, (B.MAX_LENGTH / 2)) + ')'
					WHEN 'NTEXT' THEN ''
					WHEN 'NUMERIC' THEN '(' + CONVERT(VARCHAR, B.PRECISION) + ', ' + CONVERT(VARCHAR, B.SCALE) + ')'
					WHEN 'NVARCHAR' THEN '(' + CONVERT(VARCHAR, (B.MAX_LENGTH / 2)) + ')'
					WHEN 'REAL' THEN ''
					WHEN 'SMALLDATETIME' THEN ''
					WHEN 'SMALLINT' THEN ''
					WHEN 'SMALLMONEY' THEN ''
					WHEN 'SQL_VARIANT' THEN ''
					WHEN 'TEXT' THEN ''
					WHEN 'TIMESTAMP' THEN ''
					WHEN 'TINYINT' THEN ''
					WHEN 'UNIQUEIDENTIFIER' THEN ''
					WHEN 'XML' THEN ''
					ELSE '(' + CASE WHEN CONVERT(VARCHAR,B.MAX_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(VARCHAR,B.MAX_LENGTH) END + ')' END) AS COL_TYPE,
			CASE
				WHEN B.IS_NULLABLE = '0' THEN 'N'
				ELSE 'Y'
			END AS NULLABLE,

			ISNULL(SUBSTRING(B_Default.definition, 2, LEN(B_Default.definition) - 2), '') AS COLUMN_DEFAULT,
			REPLACE(REPLACE(REPLACE(Convert(Varchar(MAX), ISNULL(C.VALUE,'')), CHAR(9), ' '), CHAR(10), ' '), CHAR(13), ' ') AS COMMENTS,
			
			A.Object_Id AS Object_Id,
    		CASE A.TYPE WHEN 'U' THEN 'TABLE'
							WHEN 'V' THEN 'VIEW'
							WHEN 'TF' THEN 'FUNCTION'
							WHEN 'FN' THEN 'FUNCTION'
							WHEN 'IF' THEN 'FUNCTION'
							WHEN 'P' THEN 'PROCEDURE'
							ELSE A.TYPE
			END AS TABTYPE

  FROM SYS.Objects AS A WITH (NOLOCK) INNER JOIN SYS.COLUMNS AS B WITH (NOLOCK) ON A.OBJECT_ID = B.OBJECT_ID
							  LEFT JOIN SYS.DEFAULT_CONSTRAINTS AS B_Default WITH (NOLOCK) ON B.Object_id = B_Default.parent_object_id AND B.column_id = B_Default.parent_column_id
  							  LEFT JOIN SYS.EXTENDED_PROPERTIES AS C WITH (NOLOCK) ON C.MAJOR_ID = B.OBJECT_ID AND C.MINOR_ID = B.COLUMN_ID AND C.NAME = 'MS_DESCRIPTION'
 WHERE A.TYPE NOT IN ('UQ', 'SQ', 'D', 'PK', 'S', 'IT', 'TR')
   AND A.NAME = @table_name
 ORDER BY B.Column_ID
END;

 

Column 정보 조회

 

위는 컬럼명을 조회한 것으로 조회한 테이블의 컬럼명, 컬럼타입, 디폴트값, 코멘트 등을 확인할 수 있다.