In a technical document I was working on today I hade 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
)