Red Gate forums :: View topic - If exist checks only go to the table level...
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

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

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
bleitheiser



Joined: 03 May 2011
Posts: 8

PostPosted: Thu Feb 09, 2012 11:37 pm    Post subject: If exist checks only go to the table level... Reply with quote

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?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Mon Feb 13, 2012 4:01 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
bleitheiser



Joined: 03 May 2011
Posts: 8

PostPosted: Mon Feb 13, 2012 4:22 pm    Post subject: Response... Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Mon Feb 13, 2012 5:17 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
bleitheiser



Joined: 03 May 2011
Posts: 8

PostPosted: Mon Feb 13, 2012 5:19 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Mon Feb 13, 2012 6:40 pm    Post subject: Reply with quote

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%.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Mon Feb 13, 2012 6:42 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
bleitheiser



Joined: 03 May 2011
Posts: 8

PostPosted: Mon Feb 13, 2012 6:44 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
bleitheiser



Joined: 03 May 2011
Posts: 8

PostPosted: Mon Feb 13, 2012 7:17 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Wed Feb 15, 2012 4:30 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
bleitheiser



Joined: 03 May 2011
Posts: 8

PostPosted: Wed Feb 15, 2012 4:48 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Wed Feb 22, 2012 12:17 pm    Post subject: Reply with quote

Thanks for your patience. I have put in a feature request: SC-5620. We will notify you if anything is done here on it.
Back to top
View user's profile Send private message
bleitheiser



Joined: 03 May 2011
Posts: 8

PostPosted: Tue Feb 28, 2012 6:26 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6643

PostPosted: Wed Feb 29, 2012 11:17 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group