Multiple IF NOT EXISTS with one wrapper?

Question

I am wondering if there is any way to do this. We have a program that does data modeling for us (IDA), and it will generate at times hundreds of different alter/delete/update statements for us. The script works, except it does not meet the requirement of being able to be ran multiple times, which we sometimes need due to devops work. I haven't been able to find a way for the modeler to automatically add an IF NOT EXISTS to each statement, and so this means manually needing to add it to each one.

Is there a way to wrap the entire script in one IF NOT EXISTS? or handle this with some other kind of loop or flag I don't know about?

Example: Currently we would have to do this:

    IF NOT EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'USERS' AND COLUMN_NAME = 'LASTNAME'
  )
   BEGIN
    ALTER TABLE DBO.USERS ADD LASTNAME CHAR(2) NULL
   END;
GO

IF NOT EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'ASSETS' AND COLUMN_NAME = 'ASSETTYPE'
  )
   BEGIN
    ALTER TABLE DBO.ASSETS ADD ASSETTYPE CHAR(2) NULL
   END;
GO

IF NOT EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = 'ADDRESS' AND COLUMN_NAME = 'LINE3'
  )
   BEGIN
    ALTER TABLE DBO.ADDRESS ADD LINE3 CHAR(2) NULL
   END;
GO

Whereas I'd like to be able to not need to add the IF NOT EXISTS to every select, just something to indicate for the script to automatically check if it exists first.

Any thoughts? Thanks.

Do you have a list of tables and columns that would need to be altered? You can utilize Dynamic SQL and loop throu each table name and column name — Kelevra, Nov 12 '19 at 22:19

score 1 · Answer 1 · answered Nov 12 '19 at 21:45

You could try:

IF COL_LENGTH('table_name','column_name') IS NULL
BEGIN
/* ALTER TABLE .... */
END

This was obtained from this forum: How to check if a column exists in a SQL Server table? (Got 958 up votes) or Add a column to a table, if it does not already exist

Kelevra · Answer 2 · 2019-11-12T22:46:58.930

You would need to load all the TableName and ColumnName combination into the #TEMP1 through which you need to loop it. Also you can change the data type of the column to a variable as well. Let me know if you need help adapting the code to your needs. You can scale that code to add DB_NAME and different datatypes. But you would need different versions of the code for update and delete

SELECT TABLENAME, COLNAME
INTO #TEMP1
FROM MYTABLE

DECLARE @TABLE_NAME VARCHAR(100)
DECLARE @COL_NAME VARCHAR(100)
DECLARE @SQL VARCHAR(MAX)

WHILE EXISTS (SELECT * FROM #TEMP1)
BEGIN

SELECT TOP 1 @TABLE_NAME = TABLENAME,
             @COL_NAME   = COLNAME
FROM #TEMP1

SET @SQL = '    
IF NOT EXISTS (
  SELECT * FROM INFORMATION_SCHEMA.COLUMNS
   WHERE TABLE_NAME = '+@TABLE_NAME+' AND COLUMN_NAME = '+@COL_NAME+'
  )
   BEGIN
    ALTER TABLE DBO.'+@TABLE_NAME+' ADD '+@COL_NAME+' CHAR(2) NULL
   END'

EXEC @SQL

DELETE FROM #TEMP1
WHERE TABLENAME = @TABLE_NAME AND COLNAME = @COL_NAME

END

Multiple IF NOT EXISTS with one wrapper?

2 Answers2