April 25, 2014 at 4:58 am
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
April 25, 2014 at 5:48 am
Great article. I do, however, want to point out that organization is spelled with a z not an s
April 25, 2014 at 7:52 am
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."
April 25, 2014 at 7:54 am
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!
April 25, 2014 at 8:50 am
Only in US English, in UK English it is spelt with an 's'
April 25, 2014 at 10:41 am
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)
April 25, 2014 at 10:46 am
jack.james.holmes (4/25/2014)
And also this error message for the ScavengeDataDictionaryFields procedureMsg 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
April 25, 2014 at 11:24 am
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.
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.
May 6, 2014 at 5:52 am
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 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply