T-SQL Function to Get Column Details from Database
In a technical document I was working on today I had to include some details about database table definitions, including the column names, data types, primary and foreign key information, and column descriptions stored in MS_Description extended property. Using information schema views and the fn_listextendedproperty function I wrote a table valued function which returns information about all the columns in the given table. I'm posting it here in case someone else finds it useful.
CREATE FUNCTION [dbo].[GetColumnDetails]
(
@tableName sysname
)
RETURNS TABLE
AS
RETURN
(
SELECT
ColumnName = C.COLUMN_NAME,
DataType = UPPER(C.DATA_TYPE) + CASE
WHEN C.CHARACTER_MAXIMUM_LENGTH = -1 THEN '(MAX)'
WHEN C.CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN
'(' + CONVERT(nvarchar(10), C.CHARACTER_MAXIMUM_LENGTH) + ')'
WHEN C.DATA_TYPE IN ('decimal', 'numeric') THEN
'(' + CONVERT(nvarchar(2), C.NUMERIC_PRECISION) + '; '
+ CONVERT(nvarchar(2), C.NUMERIC_SCALE) + ')'
ELSE ''
END
+ ', ' + CASE C.IS_NULLABLE
WHEN 'NO' THEN 'NOT NULL'
ELSE 'NULL'
END
+ CASE
WHEN PK.CONSTRAINT_NAME IS NOT NULL THEN ', PK'
ELSE ''
END
+ CASE
WHEN FK.CONSTRAINT_NAME IS NOT NULL THEN ', FK'
ELSE ''
END,
Description = D.value
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT OUTER JOIN
(SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND
TC.CONSTRAINT_TYPE = 'FOREIGN KEY') AS FK
ON C.TABLE_NAME = FK.TABLE_NAME AND C.COLUMN_NAME = FK.COLUMN_NAME
LEFT OUTER JOIN
(SELECT KCU.TABLE_NAME, KCU.COLUMN_NAME, KCU.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND
TC.CONSTRAINT_TYPE = 'PRIMARY KEY') AS PK
ON C.TABLE_NAME = PK.TABLE_NAME AND C.COLUMN_NAME = PK.COLUMN_NAME
LEFT OUTER JOIN
fn_listextendedproperty('MS_Description', 'schema', 'dbo',
'table', @tableName, 'column', default) AS D
ON D.objname COLLATE database_default =
C.COLUMN_NAME COLLATE database_default
WHERE C.TABLE_NAME = @tableName
)
