Script to list all Extended Properties on all Columns for each table in a Database

Question

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286609 More actions · Answer 1

SELECT OBJECT_NAME(ep.major_id) AS [ObjectName],

CAST(Value AS nvarchar(500)) AS [MS_Description]

FROM sys.extended_properties AS ep

WHERE ep.class = 1 AND ep.minor_id = 1

ORDER BY OBJECT_NAME(ep.major_id)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Sean,

I looked at BOL but obviously not good enough.

Thank you.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Sean,

Each table is duplicated in a different schema.

Unfortunately I do not see a way to include the schema as a column and include that in the sort.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

Well you already have the OBJECT_ID so it is pretty simple.

SELECT OBJECT_NAME(ep.major_id) AS [ObjectName],

CAST(Value AS nvarchar(500)) AS [MS_Description]

,OBJECT_SCHEMA_NAME(ep.major_id) as SchemaName

FROM sys.extended_properties AS ep

WHERE ep.class = 1 AND ep.minor_id = 1

ORDER BY OBJECT_NAME(ep.major_id), OBJECT_SCHEMA_NAME(ep.major_id)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

Thanks!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/