Extracting Extended Properties

Question

Extracting Extended Properties

Adam Aspin

SSCarpal Tunnel

Points: 4900
More actions
March 21, 2011 at 8:08 pm

#237133

Comments posted to this topic are about the item Extracting Extended Properties

Viewing 8 posts - 1 through 7 (of 7 total)

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

cjs SSC Rookie Points: 26 More actions · Answer 1

Cool. The Word template is what I've been looking for. I built an aspx app to display table columns in a grid, but could not figure out how to get our doc pages in SharePoint to embed that output.

For a particular table, I wanted the column attributes as well as the description info, so used something like this:

SELECT C.ColumnName, DataType, Length, NullsAllowed, DefaultValue, Description FROM

(SELECT COLUMN_NAME AS ColumnName,

DATA_TYPE AS DataType,

CHARACTER_MAXIMUM_LENGTH AS Length,

IS_NULLABLE AS NullsAllowed,

COLUMN_DEFAULT AS DefaultValue

FROM Information_Schema.Columns

WHERE Table_Name = @Table) AS C

LEFT OUTER JOIN

(SELECT objname AS ColumnName, value AS Description

FROM fn_listextendedproperty (Default, 'schema', 'dbo', 'table', @Table, 'column', default)

WHERE name = 'MS_Description') AS D

ON C.ColumnName COLLATE Latin1_General_CI_AS = D.ColumnName

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 2

Nice one cjs - shows how much you can do with this - and in so many ways - when you try!

Patrick2525 Mr or Mrs. 500 Points: 547 More actions · Answer 3

Great article/ series! Makes me want to start documenting my databases! 😛 (Yea, I'm way behind on that)

Your first image depicting the sys.extended_properties view looks different from mine. I'm running SS2008R2, and my columns match your article description (ie major_id, minor_id, name, value), but your image has column names of PropertyType, SchemaName, TableName, etc). Was that the wrong image, or did you join the sys.extended_properties view to other system views?

Thanks!

patrick

Adam Aspin SSCarpal Tunnel Points: 4900 More actions · Answer 4

Hi Patrick,

Thanks for the feedback, and you are quite right, this is the wrong image. I'll try & correct, but it might be a day or two...

All the best,

Adam

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Excellent article and great material. Thanks for the template and script too.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

damian.wise SSC Rookie Points: 46 More actions · Answer 6

An excellent set of articles. Does anyone know how to return the extended properties of Database Roles in the same way?

Damian

Cade Roux Ten Centuries Points: 1346 More actions · Answer 7

damian.wise (5/3/2011)
An excellent set of articles. Does anyone know how to return the extended properties of Database Roles in the same way?
Damian

Apply with:

EXEC sys.sp_addextendedproperty

@name=N'name',

@value=N'value',

@level0type=N'USER',

@level0name=N'databaserole'

Query with:

SELECT *

FROM sys.extended_properties AS xp

INNER JOIN sys.database_principals AS dp

ON xp.major_id = dp.principal_id

AND xp.class_desc = 'DATABASE_PRINCIPAL'