SQL 2005 SMO - 查找引用表
我需要将一些主键从非聚集更改为聚集,但我无法删除约束,因为它是从其他外键引用的。
如何找到引用父表中主键作为外关系一部分的表,而无需循环遍历数据库中的所有表? 我需要禁用这些限制,更改 PK 并重新启用。
更新:
我不想使用纯 SQL 来执行此操作,而仅使用 SMO。
Marc,我了解外键是因为我需要类似的东西: table.PrimaryKey.ForeignKeys(即哪些表引用我的表的主键) 我只是想避免循环遍历数据库中的所有表,并检查每个表的ForeignKeys 属性,看看它们中是否有任何一个引用我的表。(不可扩展)
I need to change some primary keys from non-clustered to clustered but I can't drop the constraint because it is referenced from other foreign keys.
How can I find the tables that reference a primary key in the parent table as part of a foreign relation without looping through all tables in the DB? I need to disable the constraints on those, change the PK and re-enable.
Update:
I do not want to use plain SQL to do this but SMO only.
Marc, I know about ForeignKeys by I need something like:
table.PrimaryKey.ForeignKeys (i.e. which tables are referencing my table's primary key)
I just want to avoid looping through all the tables in the database and check the ForeignKeys property on each and every one of them to see if any of them reference my table.(not scalable)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
好吧,我想我找到了。
或直接
table.EnumForeignKeys()
我期待一个属性而不是函数。 我很确定它在幕后按照 cmsjr 的建议执行。
Ok I think I found it.
or directly
table.EnumForeignKeys()
I was expecting a property instead of a function. I am pretty sure behind the scenes it does what cmsjr suggested.
使用 SMO,你可以这样做:
Marc
Using SMO, you could do this:
Marc
该查询应该可以工作,并且可以使用 Database.ExecuteWithResults 执行,
例如
This query should work, and could be executed using Database.ExecuteWithResults
e.g.
您可以使用
INFORMATION_SCHEMA
视图。INFORMATION_SCHEMA.TABLE_CONSTRAINTS
将为您提供该表上主键的名称。SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @TableName
给定主键名称,您可以从
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
获取使用这些键的引用约束,然后通过查询 INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
本身不是 SMO,但考虑到上述内容,您应该能够组合一个查询来列出您需要禁用的约束。
You could use the
INFORMATION_SCHEMA
Views.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
will give you the names of the primary keys on that table.SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = @TableName
Given the primary key names you can get the referential constraints that use those keys from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
And then the table names by querying
INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
Not SMO as such, but given the above you should be able to put together a query that will list the constraints you need to disable.
这对我不起作用。
考虑以下关系:
表1 --> 主表;
表2 --> 从表;
Table2.Table1_ID 是 Table1.ID 的外键
Table1.EnumForeignKeys() 返回 null。
相反,我成功地尝试了 DependencyWalker 对象。 以下代码列出了来自给定表集合的所有表。
其中“urns”是 table.Urn 的集合。
It doesn't work for me.
Consider the following relations:
Table1 --> master table;
Table2 --> slave table;
Table2.Table1_ID is a foreign key of Table1.ID
Table1.EnumForeignKeys() return null.
Instead I tried with success the DependencyWalker object. The following code list all the tables which dipend from a given collection of tables.
where "urns" is a collection of table.Urn.
您将必须遍历依赖树。
以下是使用SMO生成创建表和插入脚本的脚本。
**
**
You will have to travel through dependency tree.
Following is the script which use the SMO to generate Create table and insert script.
**
**