Oiling the gears for the data dictionary

  • And also this error message for the ScavengeDataDictionaryFields procedure

    Msg 102, Level 15, State 1, Procedure data_dictionary_find_MS_DESCRIPTION_fields, Line 52

    Incorrect syntax near ','.

    I am using SQL Server 2012

  • Great article. I do, however, want to point out that organization is spelled with a z not an s

  • Curious how adding records to a table would be any different than creating a single stored procedure to add extended properties? I used Michael Coles' T-SQL Tuesday post and rolled my own sp for our reporting team to execute multiple times for each parameter I feed in, like so:CREATE PROCEDURE [ExtProps].[spjc_insertStandardExtendedProperties] (@schema VARCHAR(25),

    @title VARCHAR(255),

    @author VARCHAR(255),

    @description VARCHAR(255),

    @businessNeed VARCHAR(255),

    @knownFlaws VARCHAR(255),

    @revisionHistory VARCHAR(255))

    AS

    BEGIN

    DECLARE @fullObjectName VARCHAR(255)

    SET @fullObjectName = @schema+'.'+@title

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname

    @Property_Name = 'Title', -- sysname

    @Property_Value = @title -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname

    @Property_Name = 'Author', -- sysname

    @Property_Value = @author -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname

    @Property_Name = 'Description', -- sysname

    @Property_Value = @description -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname

    @Property_Name = 'Business Need', -- sysname

    @Property_Value = @businessNeed -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname

    @Property_Name = 'Known flaws', -- sysname

    @Property_Value = @knownFlaws -- sql_variant

    EXECUTE ExtProps.PropInsert @Object_Name = @fullObjectName, -- sysname

    @Property_Name = 'Revision History', -- sysname

    @Property_Value = @revisionHistory -- sql_variant

    END

    Then, all I need to do is execute the one sp with the proper parameter values and I'm done. Created reports that search extended properties for whatever keyword you feed in, so we don't have to recreate the wheel if someone has already created a report that does what we're looking for. Seems just about as simple as you can get, self-documenting.

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • mbarnett3 (4/25/2014)


    Great article. I do, however, want to point out that organization is spelled with a z not an s

    Not according to my dictionary!

  • Only in US English, in UK English it is spelt with an 's'

  • This is a great start for our documentation efforts!

    I did find that there was an extra parenthesis in the code for the proc dbo.ScavengeDataDictionaryFields. Once I took it out it worked like a charm!

    FROM ::fn_listextendedproperty( (NULL, @SchemaOrUser, @SchemaName, 'table', @TableName, 'column', default)

  • jack.james.holmes (4/25/2014)


    And also this error message for the ScavengeDataDictionaryFields procedure

    Msg 102, Level 15, State 1, Procedure data_dictionary_find_MS_DESCRIPTION_fields, Line 52

    Incorrect syntax near ','.

    I am using SQL Server 2012

    There were some parenthsis issues here. I'll past my code below, see if that helps you.

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF EXISTS ( SELECT 1

    FROM ::fn_listextendedproperty(NULL, @SchemaOrUser,

    @SchemaName, 'table',

    @TableName, default,

    default))

    EXECUTE sp_updateextendedproperty N'MS_Description',

    @ObjectDescription, @SchemaOrUser, @SchemaName, N'table',

    @TableName, NULL, NULL

    ELSE

    EXECUTE sp_addextendedproperty N'MS_Description',

    @ObjectDescription, @SchemaOrUser, @SchemaName, N'table',

    @TableName, NULL, NULL

    RAISERROR ( 'DOCUMENTED TABLE: %s', 10, 1, @TableName ) WITH NOWAIT

    FETCH NEXT FROM csr_dd INTO @SchemaName, @TableName,

    @ObjectDescription

    END

  • jcrawf02 (4/25/2014)


    Curious how adding records to a table would be any different than creating a single stored procedure to add extended properties?

    There are a few reasons why I stuck with tables rather than go direct to extended properties.

    • Permissions required to run the extended properties procs. The people who maintain the descriptions might not be the ones who apply the extended properties.
    • Simple test procs against INFORMATION_SCHEMA objects.
    • Cross platform. Having ported databases to MySQL and HP Vertica the method of applying the data dictionary changes but the two data dictionary tables and contents remained the same
    • Change request process. Maintaining data in a table is bog standard stuff. Extended properties triggers a change control process....don't ask:sick:
    • Reporting on the tables. Most of the time Red-Gate SQLDoc does the job of being the presentation layer but quite often I find it useful to join the two data dictionary tables to other data gleaned from the database.

    Strictly speaking I could get the data dictionary info from the system tables. I'm nervous that Microsoft offers no guarantees with system tables.

  • Here is an alternate approach that produces a 'portable' data dictionary. Use a database to start the process, then script those extended properties to a SQL text file. THen you can reapply those properties to any database. Modify and rescript. The scripted file can be included in your version control system and tracked with the rest of your schema.

    http://dnhlmssql.blogspot.com/2014/02/replace-data-types-magic-numbers-and.html

Viewing 9 posts - 16 through 24 (of 24 total)

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