If exist checks only go to the table level...
bleitheiser
Posts: 10
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?
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?
Comments
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.
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.
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.
I am posting this, because it is NOT doing it. It is only checking the table.
Shall I provide an example?
Thanks,
Brandon
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
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
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@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
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@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
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.
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
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