Sql server ide for refactoring

Is there an IDE for SQL Server that includes refactoring?

For an example, if I have a composite primary key on a table and I change it, sql management studio will drop all foreign keys referencing to this primary key (it will warn first). Is there a tool that generates the DROP s开发者_Python百科tatements for the foreign keys and recreates them?

I would look into the SQLDeveloper product from redgate. They offer some refactoring features in their SQL Prompt product. Also take a look at the SQL Compare tool. Both are worth every penny.

I would recommend looking at the Database project type in VS2010, if you haven't already. It has a lot of features that make DB refactoring easier than working SQL Server Management Studio.

For example it does a lot of build-time validation to make sure your database objects don't reference objects which no longer exist. For example if you rename a column, it will give you build errors for FKs that reference the old column name. Also, it has very handy "compare" feature which compares the DB project scripts & databases, generates a DIFF report, and generates the scripts to move selected changes between the two (either DB project to SQL Server, or vice versa).

I'm not sure it will automatically handle your composite key example -- in other words, when you rename a column it won't fix up all references to that column throughout the project. However, since all of the database objects are kept in scripts within the project, things like column renames are just a search & replace operation. Also if you make a mistake you will get build errors when it validates the database structure. So at least makes it easy to find the places that you need to change.

There are probably more powerful tools out there (I have heard good things about redgate) but the VS2010 support for the Database project type is fairly decent.

The way your objects handle foreign key references is upon creation of the table/constraint. ON DELETE CASCADE would only be one option. You can also have it set to NULL or default.

Unless I am misunderstanding your question, it is not the environment but the object parameters that dictate this.