SQL Server: check if table column exists and delete rows

Question

I am currently writing a generic SQL Server script to clean up various databases having more / less the same table structure. This script requires me to destroy certain data from a table if this table exists in the database. here is a sample script

IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TAB1') IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TAB1' AND COLUMN_NAME = 'COL1') delete TAB1 where COL1 not in (select COL2 from TAB2); 

As a programmer, I know that the delete command will not be executed if both condition blocks are false. However, when I run it in SQL, it returns

Invalid column name 'COL1'.

Perhaps my approach is wrong. Can someone point me in the right direction?

Answer 1

The problem is that SQL Server wants to compile the entire batch before it runs it.

And it cannot compile the package because the column is missing.

So, you have to make sure that the package can compile without trying to compile the DELETE , so save it as a string and make it compile separately:

 IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TAB1') IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TAB1' AND COLUMN_NAME = 'COL1') EXEC sp_executesql 'delete TAB1 where COL1 not in (select COL2 from TAB2);' 

You said:

As a programmer, I know that the delete command will not be executed if both condition blocks are false.

Assuming, say, C # background, your original request is like making two reflection calls to determine if a type has a specific property, and then has a line of code that directly uses this property for an object of that type - if the type does not have a property, the code is not going to compile, so reflection checks will never be performed.

Answer 2

Try this option -

 DECLARE @Column SYSNAME = 'COL1' , @Table SYSNAME = 'dbo.TAB1' , @SQL NVARCHAR(MAX) IF EXISTS ( SELECT 1 FROM sys.columns c WHERE c.[object_id] = OBJECT_ID(@Table) AND c.name = @Column ) BEGIN SELECT @SQL = ' DELETE TAB1 WHERE COL1 NOT IN (SELECT COL2 FROM TAB2)' EXEC sys.sp_executesql @SQL END