Value of extended property in T4 script returns a quoted, escaped string?

  • rolandpater

    SSC Veteran

    Points: 268



    I have a T4 script which reads extended properties. The value of these properties seems to be escaped.

    Example: I have a table person with an extended property called propertyName that has the value propertyValue.

    When I read this value I get: N'propertyValue'. The value that appears in the T4 script is the escaped value.

    The script that reads the properties of the table 'person'

    <#@ template debug="false" hostspecific="false" language="C#" #>
    <#@ output extension=".sql" #>
    <#@ SqlModelDirective processor="SqlModelDirectiveProcessor" #>

    <#@ assembly name="System.Core" #>
    <#@ import namespace="System.Linq" #>

    <#@ import namespace="System.Text" #>
    <#@ import namespace="Microsoft.SqlServer.Dac" #>
    <#@ import namespace="System.Collections.Generic" #>
    <#@ import namespace="Microsoft.SqlServer.Dac.Model" #>


    foreach (var table in Model.GetObjects(DacQueryScopes.All, ModelSchema.Table).Where(x=> x.Name.Parts[1] == "person"))
    WriteLine(@"{0}", table.Name);

    foreach (var prop in table.GetReferencing().Where(x => (x.ObjectType.Name == "ExtendedProperty")))
    WriteLine(@"{0}", prop.Name);
    WriteLine(@"{0}", prop.GetProperty(ExtendedProperty.Value).GetType());
    WriteLine(@"{0}", (String)prop.GetProperty(ExtendedProperty.Value));
    WriteLine(@"{0}", ((String)prop.GetProperty(ExtendedProperty.Value)).Length);



    The result of the script:


    If I put quotes in the value, they will be escaped as double quotes. It looks like a value meant for an SQL parser.

    Is there any way to read the property value in a normal way, without the N prefix and the extra quotes?





    If I remove the quotes in the script that generates the extended property:

    EXECUTE sp_addextendedproperty @name = N'propertyName', @value = propertyValue, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'person';

    the T4 script will generate the desired output:


    But this is not a good solution of course.

  • Site Owners

    SSC Guru

    Points: 80376

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply