How far can development tools take you – part 2?

Published 12 October 10 03:19 PM | MartinBell

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.

image 

This gave me the following project:

image 

I then looked at the project properties:

image 

and changed the target database in the deployment options to point to my testDB_V1 database

image 

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.

image 

This requires you to create a new connection or edit and existing one:

image

Once completed, this creates a file for the T1 table

image

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:

image 

Right clicking on the longcolumnname column will  give you are refactor menu option, from which you can rename the column

image 

image 

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

image

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!


Filed under:

Comments

# Dew Drop – October 13, 2010 | Alvin Ashcraft's Morning Dew said on October 13, 2010 02:42 PM:

Pingback from  Dew Drop – October 13, 2010 | Alvin Ashcraft's Morning Dew

# How far can development tool take you? - Martin Bell UK SQL Server MVP said on November 22, 2010 11:20 AM:

Pingback from  How far can development tool take you? - Martin Bell UK SQL Server MVP

This Blog

SQL Blogs

Syndication