Extracting Extended Properties

  • Adam Aspin

    SSCarpal Tunnel

    Points: 4845

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

  • cjs

    SSC Rookie

    Points: 26

    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: 4845

    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

    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: 4845

    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: 281243

    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

    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: 1326

    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'

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

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