我希望这是关于如何使用 SQL 语句检查 SQL Server 2000/2005 中是否存在表的最终讨论。
当你用谷歌搜索答案时,你会得到很多不同的答案。是否有官方/向后和向前兼容的方式来做到这一点?
这里有两种可能的方法。两者中的哪一个是标准/最佳方式?
第一种方式:
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='mytablename')
SELECT 1 AS res ELSE SELECT 0 AS res;
第二种方式:
IF OBJECT_ID (N'mytablename', N'U') IS NOT NULL
SELECT 1 AS res ELSE SELECT 0 AS res;
MySQL 提供了简单的
SHOW TABLES LIKE '%tablename%';
陈述。我正在寻找类似的东西。
对于此类查询,最好始终使用 INFORMATION_SCHEMA
视图。这些视图(大部分)是跨许多不同数据库的标准视图,并且很少在版本之间更改。
要检查表是否存在,请使用:
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TheSchema'
AND TABLE_NAME = 'TheTable'))
BEGIN
--Do Stuff
END
另请注意,如果出于任何原因您需要检查临时表,您可以这样做:
if OBJECT_ID('tempdb..#test') is not null
--- temp table exists
只要我记得,我们总是使用 OBJECT_ID
样式
IF OBJECT_ID('*objectName*', 'U') IS NOT NULL
OBJECT_ID('TableName', 'U')
来保证对象是一个表。
IF EXISTS
查询。另外,在您阅读文档之前,一切都是神秘的,尤其是在 T-SQL(或任何其他变体,真的)中。
请参阅以下方法,
方法 1:使用 INFORMATION_SCHEMA.TABLES 视图
我们可以编写如下查询来检查当前数据库中是否存在客户表。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customers')
BEGIN
PRINT 'Table Exists'
END
方法 2:使用 OBJECT_ID() 函数
我们可以使用下面的 OBJECT_ID() 函数来检查当前数据库中是否存在客户表。
IF OBJECT_ID(N'dbo.Customers', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
方法 3:使用 sys.Objects 目录视图
我们可以使用 Sys.Objects 目录视图来检查 Table 是否存在,如下所示:
IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID(N'dbo.Customers') AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
方法 4:使用 sys.Tables 目录视图
我们可以使用 Sys.Tables 目录视图来检查 Table 是否存在,如下所示:
IF EXISTS(SELECT 1 FROM sys.Tables WHERE Name = N'Customers' AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
方法 5:避免使用 sys.sysobjects 系统表
我们应该避免直接使用 sys.sysobjects 系统表,在未来的某些 Sql Server 版本中将不推荐直接访问它。根据 Microsoft BOL 链接,Microsoft 建议直接使用目录视图 sys.objects/sys.tables 而不是 sys.sysobjects 系统表。
IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'Customers' AND xtype = N'U')
BEGIN
PRINT 'Table Exists'
END
引用自:http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/
在不同的数据库中查找表:
if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable')
print 'Exists'
只想提一种情况,使用 OBJECT_ID
方法可能会更容易一些。 INFORMATION_SCHEMA
视图是每个数据库下的对象 -
信息模式视图在名为 INFORMATION_SCHEMA 的特殊模式中定义。此模式包含在每个数据库中。
https://msdn.microsoft.com/en-us/library/ms186778.aspx
因此,您使用访问的所有表
IF EXISTS (SELECT 1
FROM [database].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='mytablename')
SELECT 1 AS res ELSE SELECT 0 AS res;
只会反映 [database]
中的内容。如果您想检查 另一个 数据库中的表是否存在,而不是每次都动态更改 [database]
,OBJECT_ID
将让您开箱即用地执行此操作。前任-
IF OBJECT_ID (N'db1.schema.table1', N'U') IS NOT NULL
SELECT 1 AS res ELSE SELECT 0 AS res;
效果一样好
IF OBJECT_ID (N'db2.schema.table1', N'U') IS NOT NULL
SELECT 1 AS res ELSE SELECT 0 AS res;
SQL SERVER 2016 编辑:
从 2016 年开始,Microsoft 通过将 if exists
关键字添加到 drop
语句来简化在删除之前检查不存在对象的功能。例如,
drop table if exists mytablename
将在 1 行代码中执行与 OBJECT_ID
/ INFORMATION_SCHEMA
包装器相同的操作。
IF OBJECT_ID('mytablename') IS NOT NULL
您可以使用以下代码
IF (OBJECT_ID('TableName') IS NOT NULL )
BEGIN
PRINT 'Table Exists'
END
ELSE
BEGIN
PRINT 'Table NOT Exists'
END
或者
IF (EXISTS (SELECT * FROM sys.tables WHERE [name] = 'TableName'))
BEGIN
PRINT 'Table Exists'
END
ELSE
BEGIN
PRINT 'Table NOT Exists'
END
IF EXISTS
(
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Mapping_APCToFANavigator]')
AND
type in (N'U')
)
BEGIN
-- Do whatever you need to here.
END
在上面的代码中,表名是 Mapping_APCToFANavigator
。
{ }
) 以很好地格式化和语法突出显示它!
如果您需要处理不同的数据库:
DECLARE @Catalog VARCHAR(255)
SET @Catalog = 'MyDatabase'
DECLARE @Schema VARCHAR(255)
SET @Schema = 'dbo'
DECLARE @Table VARCHAR(255)
SET @Table = 'MyTable'
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = @Catalog
AND TABLE_SCHEMA = @Schema
AND TABLE_NAME = @Table))
BEGIN
--do stuff
END
我知道这是一个老问题,但如果您打算经常调用它,我发现了这种可能性。
create procedure Table_Exists
@tbl varchar(50)
as
return (select count(*) from sysobjects where type = 'U' and name = @tbl)
go
sysname
数据类型而不是 varchar(50)
。不应使用已弃用的 sysobjects
视图并且不考虑架构。
为了开发人员和其他 DBA 的利益,只需在此处添加
接收@Tablename 作为参数的脚本
(可能包含也可能不包含 schemaname)并在 schema.table 存在时返回以下信息:
the_name object_id the_schema the_table the_type
[Facts].[FactBackOrder] 758293761 Facts FactBackOrder Table
每次我需要测试表或视图是否存在时,我都会生成此脚本以在其他脚本中使用,并且当它存在时,获取其 object_id 以用于其他目的。
当您传递空字符串、错误的模式名称或错误的表名时,它会引发错误。
例如,这可能在一个过程中并返回 -1。
例如,我的一个数据仓库数据库中有一个名为“Facts.FactBackOrder”的表。
这就是我实现这一目标的方式:
PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME()
PRINT ''
GO
SET NOCOUNT ON
GO
--===================================================================================
-- @TableName is the parameter
-- the object we want to deal with (it might be an indexed view or a table)
-- the schema might or might not be specified
-- when not specified it is DBO
--===================================================================================
DECLARE @TableName SYSNAME
SELECT @TableName = 'Facts.FactBackOrder'
--===================================================================================
--===================================================================================
DECLARE @Schema SYSNAME
DECLARE @I INT
DECLARE @Z INT
SELECT @TableName = LTRIM(RTRIM(@TableName))
SELECT @Z = LEN(@TableName)
IF (@Z = 0) BEGIN
RAISERROR('Invalid @Tablename passed.',16,1)
END
SELECT @I = CHARINDEX('.',@TableName )
--SELECT @TableName ,@I
IF @I > 0 BEGIN
--===================================================================================
-- a schema and table name have been passed
-- example Facts.FactBackOrder
-- @Schema = Fact
-- @TableName = FactBackOrder
--===================================================================================
SELECT @Schema = SUBSTRING(@TABLENAME,1,@I-1)
SELECT @TableName = SUBSTRING(@TABLENAME,@I+1,@Z-@I)
END
ELSE BEGIN
--===================================================================================
-- just a table name have been passed
-- so the schema will be dbo
-- example Orders
-- @Schema = dbo
-- @TableName = Orders
--===================================================================================
SELECT @Schema = 'DBO'
END
--===================================================================================
-- Check whether the @SchemaName is valid in the current database
--===================================================================================
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.SCHEMATA K WHERE K.[SCHEMA_NAME] = @Schema ) BEGIN
RAISERROR('Invalid Schema Name.',16,1)
END
--SELECT @Schema as [@Schema]
-- ,@TableName as [@TableName]
DECLARE @R1 TABLE (
THE_NAME SYSNAME
,THE_SCHEMA SYSNAME
,THE_TABLE SYSNAME
,OBJECT_ID INT
,THE_TYPE SYSNAME
,PRIMARY KEY CLUSTERED (THE_SCHEMA,THE_NAME)
)
;WITH RADHE_01 AS (
SELECT QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.NAME) AS [the_name]
,the_schema=SCHEMA_NAME(O.schema_id)
,the_table=O.NAME
,object_id =o.object_id
,[the_type]= CASE WHEN O.TYPE = 'U' THEN 'Table' ELSE 'View' END
from sys.objects O
where O.is_ms_shipped = 0
AND O.TYPE IN ('U','V')
)
INSERT INTO @R1 (
THE_NAME
,THE_SCHEMA
,THE_TABLE
,OBJECT_ID
,THE_TYPE
)
SELECT the_name
,the_schema
,the_table
,object_id
,the_type
FROM RADHE_01
WHERE the_schema = @Schema
AND the_table = @TableName
IF (@@ROWCOUNT = 0) BEGIN
RAISERROR('Invalid Table Name.',16,1)
END
ELSE BEGIN
SELECT THE_NAME
,THE_SCHEMA
,THE_TABLE
,OBJECT_ID
,THE_TYPE
FROM @R1
END
dbo.[hello.world ]
的表,而脚本由于多种原因找不到它。也就是说,不太可能有人想要创建这样的表,但仍然如此。无论如何,您的 THE_NAME
列被定义为 sysname', yet you try to squeeze 2
sysname` 列和一个点 (.
),全部用方括号括起来......总有一天会失败!
在 SQL Server 2000 中,您可以尝试:
IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
BEGIN
SELECT 1 AS 'res'
END
IF EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PutSchemaHere'
AND
TABLE_NAME = 'PutTableNameHere'
)
我总是这样检查。
IF OBJECT_ID('TestXML..tblCustomer') IS NOT NULL
BEGIN
PRINT 'Exist'
END
ELSE
BEGIN
PRINT 'Not Exist'
END
谢谢
对于尚未找到解决方案的任何人来说,重要的是要知道:SQL 服务器!= MYSQL。如果你想用 MYSQL 来做,那很简单
$sql = "SELECT 1 FROM `db_name`.`table_name` LIMIT 1;";
$result = mysql_query($sql);
if( $result == false )
echo "table DOES NOT EXIST";
else
echo "table exists";
在此处发布此内容是因为它是 Google 的热门内容。
从 INFORMATIONAL_SCHEME 和 OBJECT_ID 中选择时,我遇到了一些问题。我不知道这是否是 ODBC 驱动程序的问题或其他问题。来自 SQL 管理工作室的查询都可以。
这是解决方案:
SELECT COUNT(*) FROM <yourTableNameHere>
因此,如果查询失败,数据库中可能没有这样的表(或者您没有访问权限)。
检查是通过比较处理 ODBC 驱动程序的 SQL 执行程序返回的值(在我的情况下为整数)来完成的。
if (sqlexec(conectionHandle, 'SELECT COUNT(*) FROM myTable') == -1) {
// myTable doesn't exist..
}
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_CATALOG = 'Database Name' and
TABLE_NAME = 'Table Name' and
TABLE_SCHEMA = 'Schema Name') -- Database and Schema name in where statement can be deleted
BEGIN
--TABLE EXISTS
END
ELSE BEGIN
--TABLE DOES NOT EXISTS
END
你可以使用这个:
IF OBJECT_ID (N'dbo.T', N'U') IS NOT NULL
BEGIN
print 'deleted table';
drop table t
END
else
begin
print 'table not found'
end
Create table t (id int identity(1,1) not null, name varchar(30) not null, lastname varchar(25) null)
insert into t( name, lastname) values('john','doe');
insert into t( name, lastname) values('rose',NULL);
Select * from t
1 john doe
2 rose NULL
-- clean
drop table t
IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.TableName') AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
BEGIN
SELECT * FROM dbo.TableName;
END
GO
如果有人试图在 linq to sql(或特别是 linqpad)中做同样的事情,请打开选项以包含系统表和视图并执行以下代码:
let oSchema = sys.Schemas.FirstOrDefault(s=>s.Name==a.schema )
where oSchema !=null
let o=oSchema!=null?sys.Objects.FirstOrDefault (o => o.Name==a.item && o.Schema_id==oSchema.Schema_id):null
where o!=null
假设您有一个对象,该对象的名称位于名为 item 的属性中,而架构位于名为 schema 的属性中,其中源变量名称为 a
select name from SysObjects where xType='U' and name like '%xxx%' order by name
-- -- 创建过程以检查表是否存在
DELIMITER $$
DROP PROCEDURE IF EXISTS `checkIfTableExists`;
CREATE PROCEDURE checkIfTableExists(
IN databaseName CHAR(255),
IN tableName CHAR(255),
OUT boolExistsOrNot CHAR(40)
)
BEGIN
SELECT count(*) INTO boolExistsOrNot FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = databaseName)
AND (TABLE_NAME = tableName);
END $$
DELIMITER ;
-- -- 使用方法:检查表迁移是否存在
CALL checkIfTableExists('muDbName', 'migrations', @output);
我以这里创建一个视图为例。
因为 ALTER/CREATE 命令不能在 BEGIN/END 块内。您需要在创建之前测试是否存在并删除它
IF Object_ID('TestView') IS NOT NULL
DROP VIEW TestView
GO
CREATE VIEW TestView
as
. . .
GO
如果您担心权限丢失,您也可以编写 GRANT 语句并在最后重新运行这些语句。
您可以将 create/alter 包装成一个字符串并执行 EXEC - 这对于大视图可能会变得很难看
DECLARE @SQL as varchar(4000)
-- set to body of view
SET @SQL = 'SELECT X, Y, Z FROM TABLE'
IF Object_ID('TestView') IS NULL
SET @SQL = 'CREATE VIEW TestView AS ' + @SQL
ELSE
SET @SQL = 'ALTER VIEW TestView AS ' + @SQL
还有一个选项可以检查表是否跨数据库存在
IF EXISTS(SELECT 1 FROM [change-to-your-database].SYS.TABLES WHERE NAME = 'change-to-your-table-name')
BEGIN
-- do whatever you want
END
如果这是“最终”的讨论,那么应该注意,如果服务器是链接的,Larry Leonard 的脚本也可以查询远程服务器。
if exists (select * from REMOTE_SERVER.MyOtherDatabase.sys.tables where name = 'MyTable')
print 'Exists'
运行此查询以检查该表是否存在于数据库中:
IF(SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'YourTableName') IS NOT NULL
PRINT 'Table Exists';
考虑在一个数据库中,您有一个表 t1。你想在其他数据库上运行脚本,比如 - 如果 t1 存在,那么什么都不做,创建 t1。为此打开视觉工作室并执行以下操作:
右键单击 t1,然后 Script table as,然后 DROP 和 Create To,然后是 New Query Editor
你会找到你想要的查询。但是在执行该脚本之前不要忘记注释掉查询中的 drop 语句,因为如果已经有一个,您不想创建新的。
谢谢
不定期副业成功案例分享
SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TheSchema' AND TABLE_NAME LIKE '#TheTable%'
使用 LIKE 运算符