如果在两个模式中存在具有相似名称的删除表
I use this command to drop a table in sql-server 2008
我使用此命令删除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:
但是现在我在不同的模式中有2个具有相同名称的表:
[DBName].[Schema1].[Members]
And
[DBName].[Schema2].[Members]
So, what is your suggestion for check if exist this tables? How can I check table names with schema?
那么,你有什么建议检查这些表是否存在?如何使用模式检查表名?
UPDATE:
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
?
好的,有3个不同的答案,所有这些都有效,所以我不知道哪一个更好,有没有人知道使用object_id或sys.tables?
4 个解决方案
#1
1
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];
Update:
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.
sys.tables中的object_id与同一表的sysobjects中的object_id相同。并且与函数OBJECT_ID完全相同,返回相同的表名。请参阅以下说明示例。
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];
or in this way:
或以这种方式:
IF exists
(
SELECT *
FROM DBName.sys.objects
WHERE object_id = OBJECT_ID('[DBName].[SchemaName].[TableName]')
AND type = 'U'
)
DROP TABLE [DBName].[SchemaName].[TableName];
or for sql2000-styled tables:
或者对于sql2000样式的表:
IF exists
(
SELECT *
FROM DBName..sysobjects
WHERE object_id = OBJECT_ID('[DBName].[SchemaName].[TableName]')
AND xtype = 'U'
)
DROP TABLE [DBName].[SchemaName].[TableName];
更多相关文章
- 如何在SQL Server数据库模式中找到所有填充了100%空值的列?
- MySQL中的字符串模式匹配
- sqlserver 差异备份与还原示例
- sqlmap遇到url重写的示例
- 如何在MS Access查询中重复模式
- 修改sqlserver 2008 express为混合认证模式
- SQL2000数据库脱机/只读/紧急模式 修复
- DRP系统中用户增删改查(分页)Model1模式
- 安卓设计模式之_观察者模式