patternsqlMinor

Store extended properties and apply to another copy of the database

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

thepropertiesapplyextendedstoredatabaseanotherandcopy

Problem

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?

Solution

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

Code Snippets

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')

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

Context

StackExchange Database Administrators Q#93657, answer score: 2

Revisions (0)

No revisions yet.