locked
SQLCMD vars in CREATE TABLE script RRS feed

  • Question

  • Is there a way to use SQLCMD variables in a CREATE TABLE script? I am using SSDT and I have the following table definition:

    CREATE TABLE [dbo].[My_Table] (
        [ID]           INT      NOT NULL,
        [Name] INT      NOT NULL,
        [Date]          INT      NULL    
    ) WITH (DATA_COMPRESSION = PAGE);

    However, this does not deploy to LOCALDB because of the DATA_COMPRESSION option. So I defined a variable for the DATA_COMPRESSION:


    And I have updated the CREATE TABLE script to use the variable:

    CREATE TABLE [dbo].[My_Table] (
        [ID]           INT      NOT NULL,
        [Name] INT      NOT NULL,
        [Date]          INT      NULL    
    ) WITH (DATA_COMPRESSION = $(PageCompression));

    However, this gives me a build error in SSDT:

    Is it possible to do what I am trying to do? If not, please consider adding this capability in a future release of SSDT. Either that or add DATA_COMPRESSION to localdb. My goal is to not require every developer to install an Enterprise version of SQL Server just to work locally with the database project.

    Thanks.

    Monday, March 26, 2012 9:59 PM

Answers

  • It is not supported to use SQLCMD variable inside object definitions, expect when referencing 3 or 4-part name inside the script body of objects like a procedure or a view. Only in pre- and post-deployment scripts you can use SQLCMD variable at arbitrary locations.


    -GertD @ www.sqlproj.com

    Monday, April 16, 2012 7:09 AM
    Answerer

All replies

  • Chris,

    what about using a post deployment script to set the desired compression level for the table?

    ALTER TABLE T1 
    REBUILD WITH (DATA_COMPRESSION = $(PageCompression));
    

    Anders Borum / SphereWorks

    • Proposed as answer by Anders Borum Wednesday, March 28, 2012 7:27 PM
    Tuesday, March 27, 2012 9:28 AM
  • Thanks for the response.  The post-deploy script will work for now, but it would be nice to not see that table as changed every time I use Schema Compare. I know that I can "Ignore Table Options", but that is not ideal either. The SQLCMD variable would be a very nice solution if it worked.

    Wednesday, March 28, 2012 5:23 PM
  • It is not supported to use SQLCMD variable inside object definitions, expect when referencing 3 or 4-part name inside the script body of objects like a procedure or a view. Only in pre- and post-deployment scripts you can use SQLCMD variable at arbitrary locations.


    -GertD @ www.sqlproj.com

    Monday, April 16, 2012 7:09 AM
    Answerer