Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extracting Extended Properties Expand / Collapse
Author
Message
Posted Monday, March 21, 2011 8:08 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:04 AM
Points: 81, Visits: 902
Comments posted to this topic are about the item Extracting Extended Properties
Post #1081711
Posted Tuesday, March 22, 2011 7:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, March 22, 2011 7:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:04 AM
Points: 81, Visits: 902
Nice one cjs - shows how much you can do with this - and in so many ways - when you try!
Post #1081978
Posted Tuesday, March 22, 2011 9:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 1, 2013 10:16 AM
Points: 117, Visits: 180
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
Post #1082074
Posted Tuesday, March 22, 2011 9:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 5:04 AM
Points: 81, Visits: 902
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
Posted Tuesday, March 22, 2011 10:36 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:51 AM
Points: 21,747, Visits: 15,441
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


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
Posted Tuesday, May 3, 2011 4:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 10:08 PM
Points: 4, Visits: 42
An excellent set of articles. Does anyone know how to return the extended properties of Database Roles in the same way?

Damian
Post #1102760
Posted Tuesday, May 3, 2011 6:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 8:26 AM
Points: 109, Visits: 490
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 »

Add to briefcase

Permissions Expand / Collapse