3

I'm using SQL Doc to document a development copy of a database. SQL Doc uses extended properties to store user entered values. At some point, I would like to copy those extended properties to the production database. What is the best way to do this?

2 Answers 2

2

You can use this script to get the columns descriptions from your development database.

SELECT
DB_NAME(DB_ID()) as DBName
,SCHEMA_NAME(sOBJ.schema_id) as SchemaName
,sOBJ.name AS TableName
, sEXP.value AS [TableDescription]
,sOBJ.type_desc As ObjectType
,sOBJ.create_date As CreateDate
,sOBJ.modify_date As ModifyDate
FROM
[sys].[objects] AS [sOBJ]
LEFT JOIN [sys].[extended_properties] AS [sEXP]
    ON [sOBJ].[object_id] = [sEXP].[major_id]
    AND [sEXP].[minor_id] = 0
    AND [sEXP].[name] = N'MS_Description'
WHERE [sOBJ].[type] IN ('U','V','TR','P')

Then upload a table with DataBase/Instance/Table/Column/Description to your production server, and use the following script to insert the description on your server. It only works with fields that don't have a previous description, so you must filter previously.

Declare   @sql as nvarchar(max)
, @Database as nvarchar(max)
, @Instance as nvarchar(128)
, @Table as nvarchar(128)
, @Column as nvarchar(128)
, @Description as nvarchar(3000)

Declare mycursor cursor for 
Select [Database], [Instance], [Table], [Column], [Description] from [dba].    [DescripcionesTablas]



open mycursor
fetch next from mycursor into @Database, @Instance, @Table, @Column, @Description
while @@FETCH_STATUS= 0
begin
set @sql = 'EXEC [' + @Database + '].dbo.sp_addextendedproperty   
            @name = N''MS_Description'',   
            @value = ''' + @Description + ''',  
            @level0type = N''Schema'', @level0name = ' + @Instance + ',  
            @level1type = N''Table'',  @level1name = ' + @Table + ',  
            @level2type = N''Column'', @level2name = ' + @Column + ';'
EXEC (@sql)
fetch next from mycursor into @Database, @Instance, @Table, @Column, @Description
END
close mycursor
deallocate mycursor
1

I don't know of an easy way to just extract them and reapply to another target, but you could use the data in sys.extended_properties to generate a script to add the properties to another database using calls to sp_addextendedproperty.

See https://msdn.microsoft.com/en-us/library/ms177541.aspx and https://msdn.microsoft.com/en-us/library/ms180047.aspx for a bit of documentation around that.

If the target database may already contain some properties (from earlier transfers, or added for other reasons) then the script you generate may need to be more clever and check to see if it should call sp_updateextendedproperty instead (also potentially sp_dropextendedproperty to remove properties that are now deprecated).

1
  • FWIW - If you name the extended property with a date extension, such as Caption_20150223 instead of just Caption, then you can add a new entry and later review and remove (if necessary) the older entries. This does add some noise to the descriptions, of course.
    – RLF
    Feb 24, 2015 at 14:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.