SQL Server 关系隐藏在存储过程而不是架构中 [英] SQL Server relationships buried in stored procedures rather than schema

问题描述

目前，我们几乎没有参照完整性，并且有许多自联接的表(实际上也许最好将其表示为单独的表或联接的视图).

At present we have very little referential integrity, as well as having a number of tables that self-join (and indeed would perhaps better be represented as separate tables or views that joined).

这些表如何相互关联的知识隐含在存储过程的逻辑中，而不是在模式中明确显示.我们正在考虑改变这一点.

The knowledge of how these tables relate to each other is implicit in the logic of the stored procedures rather than explicit in the schema. We are considering changing this.

第一步是真正理解隐含关系并记录它们.

The first step is to actually understand the implicit relationships and document them.

所以我的问题是...

提取隐含信息的最佳方法是什么，而不是关注每个存储过程.我会考虑任何工具，编写我自己的 SQL 来查询系统表，或者使用 SQL-DMO 模型——或者实际上任何可以让计算机做更多工作而我做更少的事情.

What is the best way to extract that implicit information, short of eyeballing every stored procedure. I will consider any tools, writing my own SQL to interrogate the system tables, or utilising the SQL-DMO model - or in fact anything under the sun that lets the computer do more work and me do less.

推荐答案

如果关系仅通过 SP 中的联接来标识，那么您将不会有很多运气将其自动化.

If the relationships are only identified by joins in the SPs, then you're not going to have a lot of luck automating it.

使用分析器捕获查询以首先找到最频繁的连接可能是值得的.

It might be worthwhile capturing queries using the profiler to find the most frequent joins first.

这篇关于SQL Server 关系隐藏在存储过程而不是架构中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！