Sql-server – Move extended property comments to create table comment

sql serversql-server-2008-r2

I want to convert / import extended properties of table columns of a lot of tables to simple comments next to the table definition scripts columns in the table definition of a new SSDT database project in order to get rid of the extended properties in future but keep the column description information.

The usage of SSDT with source control makes the extended properties in the deployed version of the database obsolete.

So the description info of this:

EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Customer article number.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'OrdPos', @level2type = N'COLUMN', @level2name = N'ArticleNo';

should get added to the columns definition like to this:

CREATE TABLE [dbo].[OrdPos] (
    [ArticleNo] VARCHAR(10) NOT NULL  -- Customer article number.
...

The Create Table scripts have already been created. The extended properties are all included in those scripts below the create table statements (as imported into the project). It is way too much to do it manually.

Did by chance anyone already have a similar task to solve with a good approach for some kind of automation?

Best Answer

There is no need to change from extended props to plain comments. Since I figured out that the descriptions are displayed in the visual designer part of the table in the SSDT project's tables, this is not really required to change.

This article by Jamie Thomson explains the details and that the descriptions of the columns are getting visible there as soon the column "Description" in the designer is enabled. Also, they are automatically created into the create script if entered in the designer what is pretty cool.

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 – Full-text search in SQL Server on multiple tables fails

Hi we can query multiple tables in a full-text search by using views ,but we some advantages and disadvantages . Here in your case you created view dbo.vw_recipe_search it seems your are not using it in altered procedure when i query it i am getting results as per intended

  SELECT *
        FROM dbo.vw_recipe_search r 
        INNER JOIN FREETEXTTABLE(vw_recipe_search, *, 'salsa peas') kt
            ON r.search_id = kt.[KEY]
        ORDER BY RANK DESC;

The problem in this approach is ranking and weightage of each column , like suppose if u want to give high weight to title column than descrip you have to do a lot of work in order by rank column . We used Levenstein string comparision function to order for better results set

Related Question