Competition: What’s your favorite Redgate tool? Enter now.

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

bleitheiserbleitheiser Posts: 10
edited February 29, 2012 5:17AM in SQL Compare Previous Versions
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?

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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 DonahueBrian Donahue Posts: 6,590 New member
    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?
  • 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
  • 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
    @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
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Thanks for your patience. I have put in a feature request: SC-5620. We will notify you if anything is done here on it.
  • 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
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
Sign In or Register to comment.