SQL Server 2014 – Store and Apply Extended Properties to Another Database

sql serversql server 2014

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?

Best Answer

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

Related Solutions

Sql-server – Best way to store units in database

Since you mention hundreds of columns I would consider an EAV design. While Joe Celko warns against this, I think it may be applicable in your use case. It sounds like all of your "amounts" are numbers, so you would avoid the casting issues Joe describes and the need to make every "value" a string. It will work even better if all the amounts are whole numbers, but can work also if some are decimal. Given the Units of Measure, you could go one step further and implement an "universal data model" style model based off this article by David Hay and also outlined in his book Data Model Patterns: Conventions of Thought. This model has the additional advantage of configuring which "amounts" apply to which "things" if you need that. One additional step shown in the book on page 162 is a Unit of Measure Conversion table that you can use to convert between the different Units of Measure. Here is an example:

UOM Conversion              

UOM From    UOM To        Cal Step  Operator Factor Constant
Kilograms   Pounds        1         *        2.2
Celsius     Fahrenheit    1         *        1.8
Celsius     Fahrenheit    2         +               32

This says that to convert from Kg to Lb the first step is to multiply Kg by 2.2. There is also a constant if a conversion must also include a constant value, and the ability to create multiple steps. So when converting say Celsius to Fahrenheit you multiply Celsius by 1.8 and then add 32. The key would be the from UOM, the to UOM, and the Calculation Step.

That is my 2 cents worth. I hope these references give you some good food for thought should you ever get the chance to do a reboot on the current design.

Sql-server – SQL Server Filetable document properties

It looks like (at this time) the best you are going to be able to do is use the keywords on the property, join them up to the doc and cross your fingers it is enough.

SELECT
    keyword,
    display_term,
    column_id, document_id, property_id
FROM sys.dm_fts_index_keywords_by_property
(
    DB_ID('FileTableDB'), 
    OBJECT_ID('FileTableTb')
);

MSDN on that DMV - http://msdn.microsoft.com/en-us/library/ee677646.aspx

Here is a discussion with Bob Beauchemin and I on the topic: https://twitter.com/bobbeauch/status/275101491084292096

Bob told me about the DMV mentioned above and pretty much confirmed that it is the most likely way.

Here is the Connect ticket Bob created for this topic: https://connect.microsoft.com/SQLServer/feedback/details/773212/provide-complete-fts-property-values-in-a-dmv-side-table

We also go on to talk about how it might be better to have a preprocessor of sorts to pull the meta info out, store it, (not sure if you normalize format of your docs or not), and then reprocess if modified. Bob suggests perhaps being able to pull this off with a SQLCLR trigger/proc which would be a rather clever solution and get your hands all kinds of dirty.

Best Answer

Related Solutions

Sql-server – Best way to store units in database

Sql-server – SQL Server Filetable document properties

Related Question