Retrieving MSSQL Column Descriptions

Ignition
1

Howdy,

Having some troubles retrieving column descriptions from a MSSQL server. Attempting to get the descriptions of the columns so that if the structure is ever changed in the future it will map automatically rather than having to go and change the query’s and/or the views themselves.

Any help would be great.

SELECT COLUMN_NAME AS [Output]
    ,ORDINAL_POSITION
    ,prop.value AS [COLUMN_DESCRIPTION]
FROM INFORMATION_SCHEMA.TABLES AS tbl
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col ON col.TABLE_NAME = tbl.TABLE_NAME
INNER JOIN sys.columns AS sc ON sc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
    AND sc.NAME = col.COLUMN_NAME
LEFT JOIN sys.extended_properties prop ON prop.major_id = sc.object_id
    AND prop.minor_id = sc.column_id
    AND prop.NAME = 'MS_Description'
WHERE tbl.TABLE_NAME = 'LD_RecipeUDT'
2

Are you getting any error(s), or what’s going wrong with your query?

In general this is probably not a fruitful road to go down (in my experience) but knowing the particular error you’re seeing would help to troubleshoot.

3

@PGriffith Thanks for the reply, I did get it going. but have since decided to not go down this road.

I have decided to make a direct mapping based off, UDT tags, component names & column names. Seems to work alot nicer.

1 Like