1

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?

  • The extended properties are in the sys.extended_properties DMV. A script that generates the entire DDL for tables and all their dependencies (keys, indexes, UDTs, etc) is quite a bit more work. :) – Daniel Hutmacher Jan 16 '16 at 9:44
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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