How can you find the dependencies of a table across databases?
如何在数据库中找到表的依赖关系?
Normally sp_depends will provide information for all dependent objects on a particular objects, or through a GUI tool you can see it in a more structured way. Now dependencies shown by these methods are limited to a single database - what if we have dependents in other databases?
通常,sp_depends将为特定对象上的所有依赖对象提供信息,或者通过GUI工具以更加结构化的方式查看它。现在,这些方法显示的依赖关系仅限于一个数据库 - 如果我们在其他数据库中有依赖项,该怎么办?
Is there any query to do it?
有没有查询呢?
2 个解决方案
#1
AFAIK, SQL Server can't do this out of the box.
AFAIK,SQL Server无法开箱即用。
You'll most likely need a third-party program like Red-Gate's SQL Dependency Tracker which is supposed to do this.
您很可能需要像Red-Gate的SQL Dependency Tracker这样的第三方程序。
#2
I will leave the task of finding cross db dependencies for someone else to write about as I am about to run out the door this morning but wanted to leave a quick note on sp_depends. It is broken, don't use it. It is notoriously unreliable. As per Microsoft "Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead" it is on the way out. Here is why sp_depends is broken, it relies on creation order. Say you have two stored procedures, ProcA and ProcB. ProcB depends on ProcA. If you create ProcA THEN create ProcB sp_depends will report the dependency, but due to support for late binding, if you create ProcB, THEN create ProcA sp_depends will NOT report the dependency.
我将完成为其他人发现跨数据库依赖关系的任务,因为我即将在今天早上跑出大门,但想留下关于sp_depends的快速说明。它坏了,不要用它。众所周知,这是不可靠的。根据微软“使用sys.dm_sql_referencing_entities和sys.dm_sql_referenced_entities代替”,它正在走出困境。这就是sp_depends被破坏的原因,它依赖于创建顺序。假设您有两个存储过程,ProcA和ProcB。 ProcB依赖于ProcA。如果您创建ProcA那么创建ProcB sp_depends将报告依赖关系,但由于支持后期绑定,如果您创建ProcB,那么创建ProcA sp_depends将不报告依赖关系。
For what it's worth there are a few tools I have used that do a great job of reporting dependencies properly but my favorite so far, and one that will deal with cross db dependencies if you have access to it is Visual Studio for Database Professionals. It's fairly spendy but if you have access to it is has an excellent dependencies tool and supports light refactoring, say you change the name of a column, it can find all views and procedures, even in multiple dbs and update the column name for you across your dependencies.
对于它的价值而言,我使用了一些工具可以很好地报告依赖关系,但我最喜欢的是,如果你有权访问它,那么将处理跨数据库依赖关系的工具是Visual Studio for Database Professionals。它相当花钱但是如果你有权访问它有一个很好的依赖工具并且支持轻量级重构,比如说你改变了一个列的名字,它可以找到所有的视图和程序,甚至可以在多个dbs中为你更新列名你的依赖。
For a cheap single db GUI alternative ApexSql makes a nice tool. On the free side you could find or create a stored procedure to do the dependency checking for you but I don't have any links to help you out with that right now.
对于便宜的单一数据库GUI替代方案,ApexSql是一个很好的工具。在免费方面,您可以找到或创建存储过程来为您进行依赖性检查,但我现在没有任何链接可以帮助您解决问题。
#1
AFAIK, SQL Server can't do this out of the box.
AFAIK,SQL Server无法开箱即用。
You'll most likely need a third-party program like Red-Gate's SQL Dependency Tracker which is supposed to do this.
您很可能需要像Red-Gate的SQL Dependency Tracker这样的第三方程序。
#2
I will leave the task of finding cross db dependencies for someone else to write about as I am about to run out the door this morning but wanted to leave a quick note on sp_depends. It is broken, don't use it. It is notoriously unreliable. As per Microsoft "Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead" it is on the way out. Here is why sp_depends is broken, it relies on creation order. Say you have two stored procedures, ProcA and ProcB. ProcB depends on ProcA. If you create ProcA THEN create ProcB sp_depends will report the dependency, but due to support for late binding, if you create ProcB, THEN create ProcA sp_depends will NOT report the dependency.
我将完成为其他人发现跨数据库依赖关系的任务,因为我即将在今天早上跑出大门,但想留下关于sp_depends的快速说明。它坏了,不要用它。众所周知,这是不可靠的。根据微软“使用sys.dm_sql_referencing_entities和sys.dm_sql_referenced_entities代替”,它正在走出困境。这就是sp_depends被破坏的原因,它依赖于创建顺序。假设您有两个存储过程,ProcA和ProcB。 ProcB依赖于ProcA。如果您创建ProcA那么创建ProcB sp_depends将报告依赖关系,但由于支持后期绑定,如果您创建ProcB,那么创建ProcA sp_depends将不报告依赖关系。
For what it's worth there are a few tools I have used that do a great job of reporting dependencies properly but my favorite so far, and one that will deal with cross db dependencies if you have access to it is Visual Studio for Database Professionals. It's fairly spendy but if you have access to it is has an excellent dependencies tool and supports light refactoring, say you change the name of a column, it can find all views and procedures, even in multiple dbs and update the column name for you across your dependencies.
对于它的价值而言,我使用了一些工具可以很好地报告依赖关系,但我最喜欢的是,如果你有权访问它,那么将处理跨数据库依赖关系的工具是Visual Studio for Database Professionals。它相当花钱但是如果你有权访问它有一个很好的依赖工具并且支持轻量级重构,比如说你改变了一个列的名字,它可以找到所有的视图和程序,甚至可以在多个dbs中为你更新列名你的依赖。
For a cheap single db GUI alternative ApexSql makes a nice tool. On the free side you could find or create a stored procedure to do the dependency checking for you but I don't have any links to help you out with that right now.
对于便宜的单一数据库GUI替代方案,ApexSql是一个很好的工具。在免费方面,您可以找到或创建存储过程来为您进行依赖性检查,但我现在没有任何链接可以帮助您解决问题。