SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Adam Aspin / Extracting Extended Properties / Latest Posts

RE: Extracting Extended Properties

Cade Roux — Tue, 03 May 2011 18:23:10 GMT

[quote][b]damian.wise (5/3/2011)[/b][hr]An excellent set of articles. Does anyone know how to return the extended properties of Database Roles in the same way?Damian[/quote]Apply with:[code="sql"]EXEC sys.sp_addextendedproperty@name=N'name',@value=N'value',@level0type=N'USER',@level0name=N'databaserole'[/code]Query with:[code="sql"]SELECT *FROM sys.extended_properties AS xpINNER JOIN sys.database_principals AS dp ON xp.major_id = dp.principal_id AND xp.class_desc = 'DATABASE_PRINCIPAL'[/code]

RE: Extracting Extended Properties

damian.wise — Tue, 03 May 2011 16:35:23 GMT

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

RE: Extracting Extended Properties

SQLRNNR — Tue, 22 Mar 2011 10:36:10 GMT

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

RE: Extracting Extended Properties

Adam Aspin — Tue, 22 Mar 2011 09:52:20 GMT

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

RE: Extracting Extended Properties

Patrick2525 — Tue, 22 Mar 2011 09:45:47 GMT

Great article/ series! Makes me want to start documenting my databases! :-P (Yea, I'm way behind on that)Your first image depicting the [i]sys.extended_properties[/i] 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 [i]sys.extended_properties[/i] view to other system views?Thanks!patrick

RE: Extracting Extended Properties

Adam Aspin — Tue, 22 Mar 2011 07:35:12 GMT

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

RE: Extracting Extended Properties

cjs — Tue, 22 Mar 2011 07:28:07 GMT

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 CLEFT OUTER JOIN (SELECT objname AS ColumnName, value AS Description FROM fn_listextendedproperty (Default, 'schema', 'dbo', 'table', @Table, 'column', default) WHERE name = 'MS_Description') AS DON C.ColumnName COLLATE Latin1_General_CI_AS = D.ColumnName

Extracting Extended Properties

Adam Aspin — Mon, 21 Mar 2011 20:08:20 GMT

Comments posted to this topic are about the item [B]Extracting Extended Properties[/B]