List Extended Properties for all Tables and Columns

SQL Server 2005

Extended Properties in SQL Server give you the ablility to attach a comment or description to an object in the database. When you use Management Studio to add a Description to a table or column, it is stored as an Extended Properties with the name of MS_Description, and the object it relates to.

You can use the fn_listextendedproperty() table function to return the extended properties for the database, or a specific table or column. But what if you want to return all comments for all column and tables in the database? Well you'll need to use the system tables directly to make that happen.

To select all the extended properties for all the columns and tables in the database, use this command:

SELECT OBJECT_NAME(EXP.major_id) AS TableName, 
       C.name                    AS ColumnName, 
       EXP.name                  AS PropertyName, 
       EXP.value                 AS PropertyValue
FROM   sys.extended_properties AS EXP
LEFT OUTER JOIN sys.columns AS C
ON    C.object_id = EXP.major_id
AND   C.column_id = EXP.minor_id
WHERE EXP.class_desc = 'OBJECT_OR_COLUMN'



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






List Extended Properties for all Tables and Columns | 1 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.
List Extended Properties for all Tables and Columns
Authored by: Anonymous on Tuesday, December 13 2011 @ 05:04 AM AEDT

just what I was looking for ... whoehaa

these 9 lines of code saves me a LOT of work ..

 

cheers !

[ # ]


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.