在数百个表中查找相关列，以便将来进行关系识别 [英] Find related columns among hundreds of tables for future relational identification

问题描述

我正在使用 SQL Server 2016 从存储在 DB2 数据库中的 ERP 系统中提取信息.这有数千个表，其中没有键.从系统中提取表时，我希望能够识别表中匹配的列名，以便在构建维度时开始创建关系和键.

I am using SQL Server 2016 to pull information out of our ERP system that is stored in a DB2 database. This has thousands of tables with no keys inside of them. When pulling tables from the system, I want to be able to identify matching column names in tables so I can start creating relationships and keys when building dimensions.

有没有办法创建一个查询来搜索我的数据库中的列名并列出使用该列名的每个表?我一直在使用 OPENQUERY 和 INFORMATION_SCHEMA.TABLES 来确定我想要拉过来的表，但现在我想开始确定这些表之间的关系.

Is there a way to create a query that will search my database for column names and list every table that uses that column name? I have been using OPENQUERY and INFORMATION_SCHEMA.TABLES to determine the tables I want to pull over but now I want to start determining relationships between those tables.

任何帮助将不胜感激！

推荐答案

您可以查看旧的黄金系统表.几个例子

You can look in the old yet gold system tables. A few examples

查找列名为 ID 的所有表

find all tables with a column named like ID

select so.name, sc.name
from sys.sysobjects so
join sys.syscolumns sc on sc.id = so.id
where so.xtype = N'U'
and sc.name like 'ID%'

从表中查找 FK

select so2.name
from sys.sysobjects so
join sys.sysforeignkeys fk on so.id = fk.rkeyid
join sys.sysobjects so2 on fk.fkeyid = so2.id
where so.name = 'MyTable'

查看 MSDN 文档以获取更多参考，如果您想要任何特定组合，只需发布​​一个新问题.

Check MSDN documentation for further reference and if you want any specific combination just post a new question.

这篇关于在数百个表中查找相关列，以便将来进行关系识别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！