Query rework if possible 1

[nice95gle]

Is there a quicker way to do this? Sometimes I just can't think.

IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = 'dbo'
                       AND TABLE_NAME = 'TABLE_NAME'
                       AND COLUMN_NAME = 'Field1')
BEGIN
   ALTER TABLE dbo.TABLE_NAME 
   ADD Field1 datetime
END
-----------------------------------------------------------------
IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = 'dbo'
                       AND TABLE_NAME = 'TABLE_NAME'
                       AND COLUMN_NAME = 'Field2)
BEGIN
   ALTER TABLE dbo.TABLE_NAME
   ADD Field2 datetime
END
-----------------------------------------------------------------
IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = 'dbo'
                       AND TABLE_NAME = 'TABLE_NAME'
                       AND COLUMN_NAME = 'Field3')
BEGIN
   ALTER TABLE dbo.TABLE_NAME
   ADD Field3 int null CONSTRAINT Field3_Id_fk 
REFERENCES TABLE_NAME2 (Field_Id)
END
-----------------------------------------------------------------
IF NOT EXISTS (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                       WHERE TABLE_SCHEMA = 'dbo'
                       AND TABLE_NAME = 'TABLE_NAME'
                       AND COLUMN_NAME = 'Field4')
BEGIN
   ALTER TABLE dbo.Contact 
   ADD Field4 int null CONSTRAINT Field4_fk 
REFERENCES TABLE_NAME2 (Field_Id)
END

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

BTW...I have about 20 fields.

Well Done is better than well said
- Ben Franklin

 

[Crowley16]

can you not use a cursor for this?

--------------------
Procrastinate Now!

 

[SQLSister]

What are you trying to do? If you want to replicate the table on another environment, just script it out. If you are looking to check two tables to see if they match, then use a third party tool like SQl Compare which will do the work and write you a script to make the second table like the first.

Questions about posting. See faq183-874

 

[nice95gle]

Thanks for the reply guys and girls

Crowley16
I was thinking about a cursor, but the effort might be more than the reward.


SQLSister

I have to add 20 fields to a table. I need to send the script to the DBA for a nice error free execution. I would like to check if the field exists before I try to add it to my table. So I don't want to write the code to check this 20 times. I was wondering if there is a way to check for multiple field existence with only one statement.

Thanks

Well Done is better than well said
- Ben Franklin

 

[Crowley16]

well, you can list all the columns in the current table, and using a temp table or table variable which containst the names of all the new fields, you can left join the 2 results to see what's missing, then just add the missing fields.

--------------------
Procrastinate Now!

 

[nice95gle]

Crowley16

That could work with a little twisting and turning. If they were all the same datatype I could do a loop but that might get ugly. This one table is a test and if it works I need to do it for about 50 more tables. I'll play with that idea and see what comes of it.

Thanks



Well Done is better than well said
- Ben Franklin

 

[Crowley16]

you could include the datatype in the tables to add list, and then using dynamically generated sql, you can have your alter table statement change based on the datatype.

frankly, to set this up, you might as well go with a cursor, it wouldn't really be any more work, in fact, it could be easier;
you could include the alter statement, as a string, inside your tables to add list as well, then just curse through the table and do an if exists, exec from the results

--------------------
Procrastinate Now!