查找 SQL Server 2000 中的孤立记录
我的 ERP 数据库缺少任何外键，因此缺乏引用完整性。
我正在编写一个脚本来检查我们数据库中的大多数主要表中是否有未记录的记录。
例如，在本例中，我正在使用我们的销售表。
SLCDPM - Customer Master Table
SOMAST - Sales Order Master
SOITEM - Sales Order Items
SORELS - Sales Order Releases
基本上，对于这些（以及一大堆其他表），我需要检查 SORELS 中是否有未出现在其上方任何表中的记录。然后取出 SOITEM 并检查其上方。等等。
我开始编写脚本，但行数变得有点荒谬。我从这 4 个表开始。
select 'Sales Order Master',* from somast where fcustno not in (select fcustno from slcdpm where ftype <> 'P')
SELECT 'Sales Order Item',* FROM soitem WHERE fsono NOT IN (SELECT fsono FROM somast)
select 'Sales Order Release',* from sorels where (fsono+finumber) not in (select (fsono+finumber) from SOITEM)
我停下来的原因是我刚刚意识到 SORELS（底部表）只检查它之前的表，而不是它之前的所有表。
有人知道我可以使用一个脚本来使其更加自动化或更好的方法吗？
I am saddled with an ERP database which lacks any foreign keys and therefore lacks referential integrity.
I am writing a script to check most of the major tables in our database for ophaned records.
For example, in this case I'm working with our Sales tables.
SLCDPM - Customer Master Table
SOMAST - Sales Order Master
SOITEM - Sales Order Items
SORELS - Sales Order Releases
Basically, for these (and a whole bunch of other tables) I need to check to see if there are records in the SORELS that don't appear in any table above it. Then take SOITEM and check above it. Etc.
I started writing scripts, but the number of lines gets kind of ridiculous. Here is where I started with just these 4 tables.
select 'Sales Order Master',* from somast where fcustno not in (select fcustno from slcdpm where ftype <> 'P')
SELECT 'Sales Order Item',* FROM soitem WHERE fsono NOT IN (SELECT fsono FROM somast)
select 'Sales Order Release',* from sorels where (fsono+finumber) not in (select (fsono+finumber) from SOITEM)
The reason I stopped was that I just realized that SORELS (the bottom table) only checks the table before it, not all of the tables before it.
Anyone know of a script I can use to make this more automated or a better way to do it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
人们像这样出售数据库垃圾；我在商业产品中看到的东西总是让我感到惊讶。
我认为这是动态 sql 和游标的真实情况。这正是一种一次性管理功能，这也是这些技术存在的原因（它们主要不是用于生产代码，而是用于管理任务）。
我会创建一个表，显示每个表以及我认为应该有外键的表。 （如果系统表至少有良好的命名约定，您甚至可以从系统表中填充它）。
然后，我将使用游标浏览表并动态创建 sql，以查看 FK 表是否有孤立记录。
虽然代码仍然很多，但至少您不必全部编写。
And people sell database junk like this; it always amazes me what I see in commercial products.
This is genuine case for dynamic sql and a cursor, I think. This is exactly the kind of one-time administrative function that is reason why these techniques exist (they weren't really meant for production code mostly, but for administrative tasks).
I'd create a table showing each table and the table I think it should have a foriegn key to. (You may even be able to populate this from the system tables if they at least had a good naming convention).
Then I would use a cursor to go through the table and create the sql dymanically for looking to see if the FK table has orphaned records.
It's still alot of code but at least you didn't have to write it all.