如果在两个模式中存在具有相似名称的表，则删除表 [英] Drop table if exist with similar name in two schema

问题描述

我在 sql-server 2008 中使用这个命令删除一个表

I use this command to drop a table in sql-server 2008

IF EXISTS(SELECT name FROM [DBName]..sysobjects WHERE name = N'TableName' AND xtype='U')
DROP TABLE [DBName].[SchemaName].[TableName];

但是现在我在不同的架构中有两个同名的表:

But now I have 2 tables with same name in different schema:

[DBName].[Schema1].[Members]

和

 [DBName].[Schema2].[Members]

那么，您对检查此表是否存在的建议是什么?如何使用架构检查表名?

So, what is your suggestion for check if exist this tables? How can I check table names with schema?

更新:

好的，有 3 个不同的答案，而且它们都有效，所以我不知道哪个更好，有没有人知道使用 object_id 或 sys.tables?

OK, there is 3 different answers and all of them worked, so I don't know which one is better, does any one know about use object_id or sys.tables?

推荐答案

IF EXISTS(
  SELECT * 
  FROM [DBName].sys.tables t
  JOIN [DBName].sys.schemas s
    ON t.SCHEMA_ID = s.schema_id
  WHERE 
      t.name = N'TableName' AND t.type='U' 
  AND s.NAME = 'SchemaName'
)
  DROP TABLE [DBName].[SchemaName].[TableName];

更新:

object_id 与 sysobjects 中的 object_id 相同，用于同一个表.并且与函数 OBJECT_ID 返回的相同表名完全相同.请参阅以下说明示例.

object_id in sys.tables is the same as object_id in sysobjects for the same table. And is completely the same as function OBJECT_ID returns for the same table name. See the following illustrating examples.

因此，您可以简化查询:

So, you may simplify the query:

IF exists 
  (
    SELECT * 
    FROM DBName.sys.tables 
    WHERE object_id = OBJECT_ID('[DBName].[SchemaName].[TableName]')
    AND type = 'U'
  )
  DROP TABLE [DBName].[SchemaName].[TableName];

或以这种方式:

IF exists 
  (
    SELECT * 
    FROM DBName.sys.objects
    WHERE object_id = OBJECT_ID('[DBName].[SchemaName].[TableName]')
    AND type = 'U'
  )
  DROP TABLE [DBName].[SchemaName].[TableName];

或对于 sql2000 样式的表:

or for sql2000-styled tables:

IF exists 
  (
    SELECT * 
    FROM DBName..sysobjects
    WHERE object_id = OBJECT_ID('[DBName].[SchemaName].[TableName]')
    AND xtype = 'U'
  )
  DROP TABLE [DBName].[SchemaName].[TableName];

这篇关于如果在两个模式中存在具有相似名称的表，则删除表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！