Check if table exists, before performing operations on it?
In searching for an answer to this question, I found this popular post on StackOverflow. Unfortunately, it doesn't work completely. The question is this:
Is there a way to check for existence of a table (or another object) before performing modifications (eg INSERT )? The before mentioned post suggests this:
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'questionableTable'))
BEGIN
INSERT INTO dbo.questionableTable VALUES ('success!');
END
Error: Invalid object name 'dbo.questionableTable'.
The problem with this is that SQL Server fails when it parses the INSERT statement, stating that dbo.questionableTable doesn't exist. The previous INFORMATION_SCHEMA check doesn't seem to affect it.
Is there a way to write this kind of query? For SQL Server, in particular. But I would also like to see similar operations for other database systems, if such things exist.
The motivation behind this question is because we have multiple databases which contain subsets of each others' tables. What I would like is to have a single script that can be applied to all databases, and which only modified the tables that exist there (and doesn't error upon execution).
Use dynamic SQL via the EXEC() function:
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'questionableTable'))
BEGIN
EXEC('INSERT INTO dbo.questionableTable VALUES (''success!'')');
END
The EXEC() function executes a string as SQL, but being a string it isn't evaluated until executed, so the tables mentioned in the string don't need to exist at compile time. This allows the stored proc to be defined prior to the table being created.
我在我的本地服务器上测试了它，它似乎工作：
if exists (select * from dbname.sys.tables where name='tablename')
begin
select * from dbname.dbo.tablename
end
上一篇: 如何在存储过程中删除和创建表？
下一篇: 在执行操作之前检查表是否存在？