[MS-SQL] Table Description(명세서) 쿼리
-- Table Description ver 2.0 --
SELECT
A.TABLE_SCHEMA
, A.TABLE_NAME
, A.COLUMN_NAME
, B.ColumnDescription
, C.CONSTRAINT_NAME
, A.ORDINAL_POSITION
, A.DATA_TYPE
, CASE
WHEN A.CHARACTER_MAXIMUM_LENGTH IS NULL THEN
convert(varchar(20), isnull(A.NUMERIC_PRECISION, 0)) + ','
+ convert(varchar(20), isnull(A.NUMERIC_SCALE , 0))
ELSE convert(varchar(20), A.CHARACTER_MAXIMUM_LENGTH )
END AS LENGTH
, A.IS_NULLABLE
, ISNULL(A.COLUMN_DEFAULT, '') as [DEFAULT]
FROM INFORMATION_SCHEMA.COLUMNS AS A WITH (NOLOCK)
LEFT JOIN (
SELECT
A.name AS TableName
, B.name AS ColumnName
, C.Value AS ColumnDescription
FROM sys.tables AS A WITH (NOLOCK)
INNER JOIN sys.columns AS B WITH (NOLOCK) ON A.object_id = B.object_id
INNER JOIN sys.extended_properties AS C WITH (NOLOCK) ON A.object_id = C.major_id
AND B.column_id = c.minor_id
) AS B ON A.TABLE_NAME = B.TableName
AND A.COLUMN_NAME = B.ColumnName
LEFT JOIN (
SELECT
*
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'ISPRIMARYKEY') = 1
) C ON C.TABLE_NAME = A.TABLE_NAME
AND C.COLUMN_NAME = A.COLUMN_NAME
WHERE A.TABLE_NAME = 'TableName'