Extracting Extended Properties

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Adam Aspin » Extracting Extended Properties

Extracting Extended Properties

Rate Topic

Display Mode

Topic Options

Author

Message

Adam Aspin

Posted Monday, March 21, 2011 8:08 PM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 81, Visits: 849

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

Post #1081711

cjs

Posted Tuesday, March 22, 2011 7:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 11, 2012 6:40 AM
Points: 2, Visits: 9

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

Post #1081975

Adam Aspin

Posted Tuesday, March 22, 2011 7:35 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 81, Visits: 849

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

Post #1081978

Patrick2525

Posted Tuesday, March 22, 2011 9:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 01, 2013 10:16 AM
Points: 117, Visits: 180

Great article/ series! Makes me want to start documenting my databases! Tongue

(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

Post #1082074

Adam Aspin

Posted Tuesday, March 22, 2011 9:52 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 81, Visits: 849

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

Post #1082081

SQLRNNR

Posted Tuesday, March 22, 2011 10:36 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1082116

damian.wise

Posted Tuesday, May 03, 2011 4:35 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 06, 2013 10:26 PM
Points: 4, Visits: 38

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

Damian

Post #1102760

Cade Roux

Posted Tuesday, May 03, 2011 6:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:10 PM
Points: 105, Visits: 481

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'

Post #1102794

« Prev Topic | Next Topic »

Permissions