SQL Server : check if table column exists and remove rows
I'm currently writing a general SQL Server script to cleanup different databases having more/less the same table structure. This script requires that I wipe certain data from a table if that table exists in the database. here a sample of the 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 block are false. However, when I run it in SQL it returns
Invalid column name 'COL1'.
Probably my approach is wrong. Can anyone point me to the right direction?
The problem is, SQL Server wants to compile the entire batch before it executes any of it.
And it can't compile the batch because there's a missing column.
So you have to make sure that the batch can compile without trying to compile the DELETE statement - so keep it as a string and force it to 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 block are false.
Assuming, say, a C# background, your original query is like performing two reflection calls to determine whether a type has a particular property, and then having a line of code that directly uses that property on an object of that type - if the type doesn't have the property, the code is not going to compile, so the reflection checks never have a chance to execute.
试试这个 -
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
上一篇: 检查表中是否存在列