none
SSDT Schema Comparison from database to project consolidates keys and constraints into table definitions

    Question

  • I ran DTA and it found a lot of statistics that I should add. So I added them to the database by running the script I exported from DTA. Now I want to import those statistics to my SSDT project.

    I have a schema comparison that I use for updating my database from my project in SSDT (December 2012 on VS 2010 SP1). I ran it and it found no differences other than the statistics. I reversed the source and target and ran a comparison. The only difference it shows are the statistics. (I fully expanded the tree to be sure that statistics were the only object types listed.) But when I click on the Update button, it removes all my constraints and many keys. I noticed that the tables definitions were updated to include the constraint and key definitions. I do not want to consolidate these different object types into the table definitions in my project. This didn't happen when I did a schema comparison in this direction using a Database Project, but the behavior is different in SSDT.

    How can I do updates from a database to a project without having this consolidation take place?

    ----

    By the way, the update that consolidated the constraints and keys into the table definitions didn't create the statistics! So I changed the Schema Compare Options such that Statistics are the only Object Types selected and re-ran the Compare. Now it finds nothing that needs to be updated in my project.

    How can I get these statistics, and only these statistics, imported from by database into my SSDT project?

    - Mark



    Friday, December 28, 2012 9:00 PM

All replies

  • I thought that I'd try creating a new Schema Comparison from scratch, but when I do so, the "Project" choices for both Source Schema and Target Schema are disabled. I can only select Database or Database file. Why?

    - Mark

    Friday, December 28, 2012 9:15 PM
  • I would simply Import Script the script generated by DTA with the override option, so you do not get duplicates.

    -GertD @ www.sqlproj.com

    Saturday, December 29, 2012 6:55 PM
    Moderator
  • Gert,

    Thanks for the pointer to this feature. I was previously unaware of its existence.

    I invoked Project | Import | Script, selected the .sql file that I had exported from DTA, but found no override option. I see a check box for "Import permissions", and drop downs for "Folder Structure" and "Encoding". I left all of those at the defaults (unchecked, Schema\Object Type, and Western European) and clicked on Finish.

    Instead of creating new files under Schema Objects\Tables\Statistics, it appended the CREATE STATISTICS statements to the existing CREATE TABLE scripts in Schema Objects\Tables. Just as I don't want the constraints and keys in my table definition, I don't want the statistics there either. This creates inconsistencies with the way that the project scripts were created and maintained in the Database Projects before SSDT "replaced" those. If I want to look in my source code to find the statistics, I don't want to have to open each CREATE TABLE script. I want to be able to expand the Schema Objects\Tables\Statistics tree in Solution Explorer.

    When SSDT "upgraded" my Database Projects, it did not consolidate the existing Constraints, Keys, or Statistics scripts into the Tables scripts. It is therefore unexpected that the Schema Comparsion and Import Scripts executions only update Tables scripts and do not create or maintain the more specific script types. Do I need to file this as a Connect item?

    Meanwhile, I am not looking forward to having to create around 100 CREATE statistics scripts without assistance from a tool. I realize that I can write my own Powershell script to parse the DTA-produced .sql file into individual CREATE STATISTICS scripts, then use "Add Items to Folder" in Source Control Explorer, and add use "Add Existing Item..." to add them to my Project, but this seems like a lot of work that should be done for me. And the Database Projects feature that SSDT superceded used to do all that for me.

    Thanks,
    Mark

    Monday, December 31, 2012 3:02 PM
  • Argg, you are right, it does not help when you know the old product and they remove the options from the UI. You can do this through the command window using:

    >Project.SSDTImportScript /FileName d:\temp\cots.sql /encoding Unicode /Overwrite true /IgnoreExtendedProperties true /IgnorePermissions true /AddImportedPermissionsToModel true

    Regarding the file placement, if you want to maintain the structure you described, Schema Compare is not going to help you either. Both Import Script and Schema Compare will place objects based on one of the three option defined, none which include the setting you want.

     


    -GertD @ www.sqlproj.com

    Monday, December 31, 2012 6:30 PM
    Moderator
  • Gert,

    I created this Connect item: https://connect.microsoft.com/SQLServer/feedback/details/775510/add-support-for-the-full-folder-structure-from-database-projects-into-ssdt

    I object to neither having the inability to maintain these individual script types using Import Script and Schema Comparisons nor them being consolidated as part of the Database Project to SSDT Project "upgrade" process, as this leaves me with inconsistent handling of objects depending upon whether they were created before or after the "upgrade" to SSDT. I object to this decrease in functionality not being specifically described in the documentation for SSDT.

    It seems that SSDT was designed with relatively little thought given to the installed base of Database Project (Data Dude) users. It is great if you are starting a new Project from scratch, but converting existing projects reduced functionality in many ways. If I had known when SSDT was released how much I was going have to give up to use it, I would have stayed with Database Projects until SSDT was more feature complete when compared with Database Projects.

    Thanks,
    Mark




    Monday, December 31, 2012 7:34 PM
  • You can do this through the command window using:

    >Project.SSDTImportScript /FileName d:\temp\cots.sql /encoding Unicode /Overwrite true /IgnoreExtendedProperties true /IgnorePermissions true /AddImportedPermissionsToModel true

    How do I get a Command window that can successfully run this command?

    Thanks,
    Mark

    Monday, December 31, 2012 7:45 PM
  • Gert,

    Another issue is that when the CREATE STATISTICS commands are added to the CREATE TABLE script file, they are not listed in the new right pane of that tab, where the Keys, Check Constraints, Indexes, Foreign Keys, and Triggers are listed:

    - Mark

    Thursday, January 10, 2013 6:31 PM