How far can development tools take you – part 2?
After my last post about how poor the SQL generated by the schema compare tool in Visual Studio 2010 was. I thought I would look at how a database project would handle the same situation. So I fired up Visual Studio and created a blank SQL Server 2008 database project called DBProject.
This gave me the following project:
I then looked at the project properties:
and changed the target database in the deployment options to point to my testDB_V1 database
As I have created the database already I imported the objects from the database, but you could add the source files from your source code control system.
This requires you to create a new connection or edit and existing one:
Once completed, this creates a file for the T1 table
The file contains the following SQL code:
CREATE TABLE [dbo].[T1] (
[id] INT NOT NULL,
[longcolumnname] CHAR (1) NOT NULL,
[othercolumn] CHAR (1) NULL,
PRIMARY KEY CLUSTERED ([id] ASC) WITH
(ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)
);
If you now try to deploy the solution you will get a script that checks the server/database exists and sets the database options.
Looking at the schema view you can expand the nodes into the columns of the T1 table:
Right clicking on the longcolumnname column will give you are refactor menu option, from which you can rename the column
Now if you create a deployment script it will give you the following SQL statements to rename the column:
PRINT N'The following operation was generated from a refactoring log file 113fc586-f76d-41bd-aa2e-934496ff3852';
PRINT N'Rename [dbo].[T1].[longcolumnname] to shortercolumnname';
GO
EXECUTE sp_rename @objname = N'[dbo].[T1].[longcolumnname]', @newname = N'shortercolumnname', @objtype = N'COLUMN';
GO
This is far better than the schema compare tool, but then it does have knowledge about the context in which the change has been made.
So what about renaming the system generated PK?
If you try to change the name in the same way, you will not get the rename option on the refactor menu
If you try to change it using by modifying the script to the following SQL:
CREATE TABLE [dbo].[T1] (
[id] INT NOT NULL,
[shortercolumnname] CHAR (1) NOT NULL,
[othercolumn] CHAR (1) NULL,
CONSTRAINT PK_T1 PRIMARY KEY CLUSTERED ([id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF)
);
The subsequent deployment script will contain the following SQL to drop and recreate the Primary Key:
PRINT N'Dropping PK__T1__3213E83F7F60ED59...';
GO
ALTER TABLE [dbo].[T1] DROP CONSTRAINT [PK__T1__3213E83F7F60ED59];
GO
PRINT N'Creating PK_T1...';
GO
ALTER TABLE [dbo].[T1]
ADD CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([id] ASC) WITH
ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
GO
Because of the issue scripting renamed system generated primary keys that I mentioned in my previous post, this may be the solution you decide to use, hopefully it won’t take to long to run!