If exist checks only go to the table level...

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

If exist checks only go to the table level...

Postby bleitheiser » Thu Feb 09, 2012 11:37 pm

I am trying to figure out the easiest way to upgrade all of my customers to the latest schema.

I thought of a way to do this but I am having trouble getting it to work using SQL Compare.

What I want to do is compare an empty database. (Just an empty "shell" no tables, no stored procedures etc...) I want to have it do If not exists checks before creating/altering.

The if not exists works perfectly for creating tables, but, what if the table exists, but the column does not. This alter would be passed up because the if exists is only at the table level.

My theory is if we were able to get if not exists checks down to the column level, I could then run this script that I generated, From my latest build database to an empty database, on any customer database no matter what version they were on, and it would upgrade them all the way to the latest version.

Am I doing something wrong, or missing an option somewhere, or is this just not available at this time?
bleitheiser
 
Posts: 8
Joined: Tue May 03, 2011 5:00 pm

Postby Brian Donahue » Mon Feb 13, 2012 4:01 pm

Hello,

I don't think that's possible - SQL Server only lets you check for an object at the object level. Can you please explain the problem you're coming up against? You can get the information you need from SQL Server's information schema if you really need it but if you're adding or removing columns from a table you are always going to accomplish this by altering the table with an ALTER TABLE query.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Response...

Postby bleitheiser » Mon Feb 13, 2012 4:22 pm

Hi Brian,

SQL Server let's you check the column to see if it exists...

The problem is if you use the SQL Compare 10 option "Add object exsistence checks" it only checks the object at the table level. Meaning, it only checks to see if the table exists. If it does, then it does nothing and moves on, if it does not exist then it creates the table. But really, there is functionality lacking here.

What if the table exists, but the column does not?

To me the workflow should be like this:

Check if table exists
If false then create table with all columns.
If TRUE then do this:
Check each column in the table to see if it exists
If it does not exist
alter the table to create the new column
If it does exist
check the next column in line.

This would be really helpful if a user in unable to get the customers database to do a compare on it. This way, you could easily create one script to upgrade any customer to the latest no matter what version their database was on.
bleitheiser
 
Posts: 8
Joined: Tue May 03, 2011 5:00 pm

Postby Brian Donahue » Mon Feb 13, 2012 5:17 pm

Ah you're right you could select if exists from syscolumns. OK.

But as far as I know what you describe is exactly what SQL Compare does. If alters the table and adds a column. Exception being the 5 or 6 special cases when it has to rebuild the table due to constraints caused by SQL Server itself.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby bleitheiser » Mon Feb 13, 2012 5:19 pm

Hi Brian,

I am posting this, because it is NOT doing it. It is only checking the table.

Shall I provide an example?

Thanks,

Brandon
bleitheiser
 
Posts: 8
Joined: Tue May 03, 2011 5:00 pm

Postby Brian Donahue » Mon Feb 13, 2012 6:40 pm

Yes, that is correct. I'm not arguing with that. I just don't really understand why you need to check that the column exists. If the table exists, then the table will be altered to add or remove the column and the query will succeed 100%.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Mon Feb 13, 2012 6:42 pm

You must mean check the column and if it already exists in the table, don't try to add it and if it doesn't exist, don't try to delete it?
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby bleitheiser » Mon Feb 13, 2012 6:44 pm

Hi Brian,

It is NOT being altered. There is only a check if the table exist. Right now, it checks if the table exists don't do anything else create it...

It is NOT checking the column level after checking the table level.

Brandon
bleitheiser
 
Posts: 8
Joined: Tue May 03, 2011 5:00 pm

Postby bleitheiser » Mon Feb 13, 2012 7:17 pm

Here is a little bit of the script that i just created...

PRINT N'Creating [dbo].[AccessorialDefinition]'
GO
IF OBJECT_ID(N'[dbo].[AccessorialDefinition]', 'U') IS NULL
CREATE TABLE [dbo].[AccessorialDefinition]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[AccessorialCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccessorialDescription] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CarrierCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CarrierType] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PlantID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AccessorialCalculationMethodCode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RangeType] [int] NULL,
[Active] [bit] NULL,
[AccessorialRateKeyID] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_AccessorialDefinition] on [dbo].[AccessorialDefinition]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'PK_AccessorialDefinition' AND object_id = OBJECT_ID(N'[dbo].[AccessorialDefinition]'))
ALTER TABLE [dbo].[AccessorialDefinition] ADD CONSTRAINT [PK_AccessorialDefinition] PRIMARY KEY CLUSTERED ([ID])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[stp_InsertAccessorialDefinition]'
GO
IF OBJECT_ID(N'[dbo].[stp_InsertAccessorialDefinition]', 'P') IS NULL

see how after checking if the table exists, it then goes to creating the stored procedures.

Thanks,

Brandon
bleitheiser
 
Posts: 8
Joined: Tue May 03, 2011 5:00 pm

Postby Brian Donahue » Wed Feb 15, 2012 4:30 pm

Hi Brandon.

I guess what you want is in the case that the table *does* exist and a column needs to be added or dropped it checks syscolumns first.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby bleitheiser » Wed Feb 15, 2012 4:48 pm

Hi Brian,

Yes, that is exactly what I am looking for. If the table exists, then it would check to see if each column in that table exists, and if not then it would alter the table.

Thanks,

Brandon
bleitheiser
 
Posts: 8
Joined: Tue May 03, 2011 5:00 pm

Postby Brian Donahue » Wed Feb 22, 2012 12:17 pm

Thanks for your patience. I have put in a feature request: SC-5620. We will notify you if anything is done here on it.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby bleitheiser » Tue Feb 28, 2012 6:26 pm

Hi Brian,

How do I know if this is going to be an accepted enhancement? If possible, I would like to know if this is going to be done, or if I should look for other options.

Thanks,

Brandon
bleitheiser
 
Posts: 8
Joined: Tue May 03, 2011 5:00 pm

Postby Brian Donahue » Wed Feb 29, 2012 11:17 am

I've submitted the request, so it's up to the management whether they want to do it or not. Frankly these sorts of decisions are not made quickly around here and mostly depend the volume on user-demand.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests