All we need is an easy explanation of the problem, so here it is.
I have a situation where I need to run a query on a large number of similar databases. Most of them will have a table that I need to update some rows in but some of them will not have the table.
I want to write a query that has an
IF statement checking if the table exists without needing to use
EXEC sp_executesql for the
UPDATE statement. Is there a way to do this in SQL Server?
Here is an example of what I’m doing today:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[MyTableName]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN -- this is what I want to be able to do but cannot UPDATE [dbo].[MyTableName] SET [SomeColumn] = NULL WHERE [SomeColumn] NOT IN (1, 2, 3) -- this is the only way I know to do this without an error EXEC sp_executesql N'UPDATE [dbo].[MyTableName] SET [SomeColumn] = NULL WHERE [SomeColumn] NOT IN (1, 2, 3)' END
I may have more copies of this if statement checking for different tables tables existing and updating them but no other logic in the batch. Without
sp_executesql it gives me an error about the table not existing because it needs to compile the entire batch before evaluating the
I’m not opposed to
sp_executesql specifically, but rather dynamic SQL generally. I would like a solution that doesn’t require me to encode/escape my inner query as a string and allows me to use an IDE to provide syntax highlighting and autocompletion while writing the inner query.
How to solve :
The issue here is that SQL Server validates the objects when it parses the SQL statement and so if the object doesn’t exist it fails, however, there is a trick to getting around this.
SQL Server parses object names in the IF statement, but does not validate objects in the ELSE portion. Instead of checking if the object exists and then taking action if it does, you check if the object does not exist, then take action if it does.
CREATE TABLE TestTable1 (RowData VARCHAR(255)) GO INSERT INTO TestTable1 VALUES ('Test data') GO IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TestTable2') BEGIN PRINT 'Table does not exist' END ELSE BEGIN UPDATE TestTable2 SET RowData = 'Text update' END IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TestTable1') BEGIN PRINT 'Table does not exist' END ELSE BEGIN UPDATE TestTable1 SET RowData = 'Text update' END
In the example, TestTable2 doesn’t exist, however, the script still executes because the missing table is referenced in the ELSE portion of the IF statement. This allows the script to be parsed and when it is executed it never reaches the portion of the script with the missing object and so does not cause an error.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂