我只能通过存储过程看到这项工作,而且我无法理解语法。我基本上有一些外部系统调用SQL查询,我想首先使用类似以下的代码来验证表是否存在:
if (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'OrderUpdates'))
BEGIN
--return 1 or 0 if it exists or doesn't exist
END
我无法使返回语句正常工作。
SELECT CAST(COUNT(*) AS BIT) -- Should be unique to give 0 or 1 as result
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'OrderUpdates'
-- Not sure if you want views or not:
AND TABLE_TYPE = 'BASE TABLE'
我不太明白你得到了什么-RETURN只在存储过程或函数中有效。
如果你想在一个集合中使用相同的东西(因为调用者需要一个行集),那么类似这样的东西可以工作:
SELECT 1 AS DoesItExist
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'OrderUpdates'
UNION ALL
SELECT 0
WHERE NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'OrderUpdates')
SQL Server有一个名为"tables"的表,其中包含数据库中的表列表。所以下面的代码应该会有所帮助:
CREATE PROCEDURE DoesTableExist(@table VARCHAR(50), @exists BIT OUTPUT)
AS
BEGIN
IF EXISTS(SELECT * FROM sys.tables WHERE name = @table)
SET @exists = 1
ELSE
SET @exists = 0
RETURN @exists
END
您可以这样做:
USE SomeDatabase;
IF OBJECT_ID('YourTable', 'U') IS NOT NULL
BEGIN
SELECT 1
END ELSE BEGIN
SELECT 0
END
如果您正在查找用户表以外的类型,则可以将OBJECT_ID
中的"U"更改为由sys.objects
中列出的类型确定的正确值