SQL Server : relationship between columns in different tables
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I would like to find a way to understand if there is a relationship between two columns present in two different tables.
For example in the table [Sales].[SalesOrderHeader], I have a column SalesOrderID and in another table [Person].[EmailAddress], there is BusinessEntityID.
How can I check to see if there is a table that creates a relationship between these 2 columns? Or how can I be sure that there is not a relationship between these 2 columns?
sql-server database edited Jan 4 at 7:14
marc_s
584k13011241272
asked Jan 4 at 0:17
ema7606ema7606
41
add a comment |
I would like to find a way to understand if there is a relationship between two columns present in two different tables.
For example in the table [Sales].[SalesOrderHeader], I have a column SalesOrderID and in another table [Person].[EmailAddress], there is BusinessEntityID.
How can I check to see if there is a table that creates a relationship between these 2 columns? Or how can I be sure that there is not a relationship between these 2 columns?
sql-server database edited Jan 4 at 7:14
marc_s
584k13011241272
asked Jan 4 at 0:17
ema7606ema7606
41
Have you triedINFORMATION_SCHEMA.KEY_COLUMN_USAGE?
– Dai
Jan 4 at 0:24
Are you using any software to view tables? Some software like MS Access allow you to see relationships.
– Abu Nooh
Jan 4 at 0:28
Can you be sure that the designer of the database actually added foreign key constraints? Is there no design information regarding this database available?
– Dale Burrell
Jan 4 at 0:36
3
Possible duplicate of How to find foreign key dependencies in SQL Server?
– Vijunav Vastivch
Jan 4 at 0:42
add a comment |
I would like to find a way to understand if there is a relationship between two columns present in two different tables.
For example in the table [Sales].[SalesOrderHeader], I have a column SalesOrderID and in another table [Person].[EmailAddress], there is BusinessEntityID.
How can I check to see if there is a table that creates a relationship between these 2 columns? Or how can I be sure that there is not a relationship between these 2 columns?
sql-server database edited Jan 4 at 7:14
marc_s
584k13011241272
asked Jan 4 at 0:17
ema7606ema7606
41
I would like to find a way to understand if there is a relationship between two columns present in two different tables.
For example in the table [Sales].[SalesOrderHeader], I have a column SalesOrderID and in another table [Person].[EmailAddress], there is BusinessEntityID.
How can I check to see if there is a table that creates a relationship between these 2 columns? Or how can I be sure that there is not a relationship between these 2 columns?
sql-server database sql-server database edited Jan 4 at 7:14
marc_s
584k13011241272
asked Jan 4 at 0:17
ema7606ema7606
41
edited Jan 4 at 7:14
marc_s
584k13011241272
asked Jan 4 at 0:17
ema7606ema7606
41
edited Jan 4 at 7:14
marc_s
584k13011241272
edited Jan 4 at 7:14
marc_s
584k13011241272
edited Jan 4 at 7:14
marc_s
584k13011241272
584k13011241272
asked Jan 4 at 0:17
ema7606ema7606
41
asked Jan 4 at 0:17
ema7606ema7606
41
asked Jan 4 at 0:17
ema7606ema7606
41
41
Have you triedINFORMATION_SCHEMA.KEY_COLUMN_USAGE?
– Dai
Jan 4 at 0:24
Are you using any software to view tables? Some software like MS Access allow you to see relationships.
– Abu Nooh
Jan 4 at 0:28
Can you be sure that the designer of the database actually added foreign key constraints? Is there no design information regarding this database available?
– Dale Burrell
Jan 4 at 0:36
3
Possible duplicate of How to find foreign key dependencies in SQL Server?
– Vijunav Vastivch
Jan 4 at 0:42
add a comment |
Have you triedINFORMATION_SCHEMA.KEY_COLUMN_USAGE?
– Dai
Jan 4 at 0:24
Are you using any software to view tables? Some software like MS Access allow you to see relationships.
– Abu Nooh
Jan 4 at 0:28
Can you be sure that the designer of the database actually added foreign key constraints? Is there no design information regarding this database available?
– Dale Burrell
Jan 4 at 0:36
3
Possible duplicate of How to find foreign key dependencies in SQL Server?
– Vijunav Vastivch
Jan 4 at 0:42
Have you tried
INFORMATION_SCHEMA.KEY_COLUMN_USAGE?– Dai
Jan 4 at 0:24
Have you tried
INFORMATION_SCHEMA.KEY_COLUMN_USAGE?– Dai
Jan 4 at 0:24
Are you using any software to view tables? Some software like MS Access allow you to see relationships.
– Abu Nooh
Jan 4 at 0:28
Are you using any software to view tables? Some software like MS Access allow you to see relationships.
– Abu Nooh
Jan 4 at 0:28
Can you be sure that the designer of the database actually added foreign key constraints? Is there no design information regarding this database available?
– Dale Burrell
Jan 4 at 0:36
Can you be sure that the designer of the database actually added foreign key constraints? Is there no design information regarding this database available?
– Dale Burrell
Jan 4 at 0:36
3
3
Possible duplicate of How to find foreign key dependencies in SQL Server?
– Vijunav Vastivch
Jan 4 at 0:42
Possible duplicate of How to find foreign key dependencies in SQL Server?
– Vijunav Vastivch
Jan 4 at 0:42
add a comment |
3 Answers
3
active
oldest
votes
INFORMATION_SCHEMA is what you are looking for. You can see whether or not a given column is used in a constraint by executing
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'BusinessEntityID'
You will have to do some additional work to focus on your specific solution, but this is where to start.
answered Jan 4 at 0:27
competent_techcompetent_tech
40.2k106599
add a comment |
You could do one the following to find the tables that reference [Sales].[SalesOrderHeader]:
EXEC sp_fkeys @pktable_name = N'SalesOrderHeader',@pktable_owner = N'Sales';
answered Jan 4 at 0:38
JM_JM_
75639
add a comment |
I apologize before hand for what follows:
create table #rels (rel_name varchar(max), matches int) declare @sql varchar(max) = '' select @sql+= char(10) + 'insert into #rels select ''' tbla + '.' + col_a + '.' + tbl_b + '.' col_b ''' colrel, count(*) from ' + tbl_a + ' join ' + tbl_b + ' on cast(' + col_a + ' as varchar(max)) = cast(' + col_b + ' as varchar(max)) from ( select a.column_name col_a, object_name(a.object_id) tbl_a, b.column_name col_b, object_name(b.object_id) tbl_b from sys.columns a cross apply sys.columns b where a.column_name <> b.column_name where a.system_type_id = b.system_type_id ) cols exec (@sql) select * from #rels where matches > 0 order by matches desc drop table #rels
answered Jan 4 at 1:02
Dale LudwigDale Ludwig
815
1
Some explanation of this code and the occasional CR would help a lot here.
– scsimon
Jan 4 at 1:19
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54031590%2fsql-server-relationship-between-columns-in-different-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
INFORMATION_SCHEMA is what you are looking for. You can see whether or not a given column is used in a constraint by executing
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'BusinessEntityID'
You will have to do some additional work to focus on your specific solution, but this is where to start.
answered Jan 4 at 0:27
competent_techcompetent_tech
40.2k106599
add a comment |
INFORMATION_SCHEMA is what you are looking for. You can see whether or not a given column is used in a constraint by executing
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'BusinessEntityID'
You will have to do some additional work to focus on your specific solution, but this is where to start.
answered Jan 4 at 0:27
competent_techcompetent_tech
40.2k106599
add a comment |
INFORMATION_SCHEMA is what you are looking for. You can see whether or not a given column is used in a constraint by executing
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'BusinessEntityID'
You will have to do some additional work to focus on your specific solution, but this is where to start.
answered Jan 4 at 0:27
competent_techcompetent_tech
40.2k106599
INFORMATION_SCHEMA is what you are looking for. You can see whether or not a given column is used in a constraint by executing
SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'Person' AND COLUMN_NAME = 'BusinessEntityID'
You will have to do some additional work to focus on your specific solution, but this is where to start.
answered Jan 4 at 0:27
competent_techcompetent_tech
40.2k106599
answered Jan 4 at 0:27
competent_techcompetent_tech
40.2k106599
answered Jan 4 at 0:27
competent_techcompetent_tech
40.2k106599
answered Jan 4 at 0:27
competent_techcompetent_tech
40.2k106599
40.2k106599
add a comment |
add a comment |
You could do one the following to find the tables that reference [Sales].[SalesOrderHeader]:
EXEC sp_fkeys @pktable_name = N'SalesOrderHeader',@pktable_owner = N'Sales';
answered Jan 4 at 0:38
JM_JM_
75639
add a comment |
You could do one the following to find the tables that reference [Sales].[SalesOrderHeader]:
EXEC sp_fkeys @pktable_name = N'SalesOrderHeader',@pktable_owner = N'Sales';
answered Jan 4 at 0:38
JM_JM_
75639
add a comment |
You could do one the following to find the tables that reference [Sales].[SalesOrderHeader]:
EXEC sp_fkeys @pktable_name = N'SalesOrderHeader',@pktable_owner = N'Sales';
answered Jan 4 at 0:38
JM_JM_
75639
You could do one the following to find the tables that reference [Sales].[SalesOrderHeader]:
EXEC sp_fkeys @pktable_name = N'SalesOrderHeader',@pktable_owner = N'Sales';
answered Jan 4 at 0:38
JM_JM_
75639
answered Jan 4 at 0:38
JM_JM_
75639
answered Jan 4 at 0:38
JM_JM_
75639
answered Jan 4 at 0:38
JM_JM_
75639
75639
add a comment |
add a comment |
I apologize before hand for what follows:
create table #rels (rel_name varchar(max), matches int) declare @sql varchar(max) = '' select @sql+= char(10) + 'insert into #rels select ''' tbla + '.' + col_a + '.' + tbl_b + '.' col_b ''' colrel, count(*) from ' + tbl_a + ' join ' + tbl_b + ' on cast(' + col_a + ' as varchar(max)) = cast(' + col_b + ' as varchar(max)) from ( select a.column_name col_a, object_name(a.object_id) tbl_a, b.column_name col_b, object_name(b.object_id) tbl_b from sys.columns a cross apply sys.columns b where a.column_name <> b.column_name where a.system_type_id = b.system_type_id ) cols exec (@sql) select * from #rels where matches > 0 order by matches desc drop table #rels
answered Jan 4 at 1:02
Dale LudwigDale Ludwig
815
1
Some explanation of this code and the occasional CR would help a lot here.
– scsimon
Jan 4 at 1:19
add a comment |
I apologize before hand for what follows:
create table #rels (rel_name varchar(max), matches int) declare @sql varchar(max) = '' select @sql+= char(10) + 'insert into #rels select ''' tbla + '.' + col_a + '.' + tbl_b + '.' col_b ''' colrel, count(*) from ' + tbl_a + ' join ' + tbl_b + ' on cast(' + col_a + ' as varchar(max)) = cast(' + col_b + ' as varchar(max)) from ( select a.column_name col_a, object_name(a.object_id) tbl_a, b.column_name col_b, object_name(b.object_id) tbl_b from sys.columns a cross apply sys.columns b where a.column_name <> b.column_name where a.system_type_id = b.system_type_id ) cols exec (@sql) select * from #rels where matches > 0 order by matches desc drop table #rels
answered Jan 4 at 1:02
Dale LudwigDale Ludwig
815
1
Some explanation of this code and the occasional CR would help a lot here.
– scsimon
Jan 4 at 1:19
add a comment |
I apologize before hand for what follows:
create table #rels (rel_name varchar(max), matches int) declare @sql varchar(max) = '' select @sql+= char(10) + 'insert into #rels select ''' tbla + '.' + col_a + '.' + tbl_b + '.' col_b ''' colrel, count(*) from ' + tbl_a + ' join ' + tbl_b + ' on cast(' + col_a + ' as varchar(max)) = cast(' + col_b + ' as varchar(max)) from ( select a.column_name col_a, object_name(a.object_id) tbl_a, b.column_name col_b, object_name(b.object_id) tbl_b from sys.columns a cross apply sys.columns b where a.column_name <> b.column_name where a.system_type_id = b.system_type_id ) cols exec (@sql) select * from #rels where matches > 0 order by matches desc drop table #rels
answered Jan 4 at 1:02
Dale LudwigDale Ludwig
815
I apologize before hand for what follows:
create table #rels (rel_name varchar(max), matches int) declare @sql varchar(max) = '' select @sql+= char(10) + 'insert into #rels select ''' tbla + '.' + col_a + '.' + tbl_b + '.' col_b ''' colrel, count(*) from ' + tbl_a + ' join ' + tbl_b + ' on cast(' + col_a + ' as varchar(max)) = cast(' + col_b + ' as varchar(max)) from ( select a.column_name col_a, object_name(a.object_id) tbl_a, b.column_name col_b, object_name(b.object_id) tbl_b from sys.columns a cross apply sys.columns b where a.column_name <> b.column_name where a.system_type_id = b.system_type_id ) cols exec (@sql) select * from #rels where matches > 0 order by matches desc drop table #rels
answered Jan 4 at 1:02
Dale LudwigDale Ludwig
815
answered Jan 4 at 1:02
Dale LudwigDale Ludwig
815
answered Jan 4 at 1:02
Dale LudwigDale Ludwig
815
answered Jan 4 at 1:02
Dale LudwigDale Ludwig
815
815
1
Some explanation of this code and the occasional CR would help a lot here.
– scsimon
Jan 4 at 1:19
add a comment |
1
Some explanation of this code and the occasional CR would help a lot here.
– scsimon
Jan 4 at 1:19
1
1
Some explanation of this code and the occasional CR would help a lot here.
– scsimon
Jan 4 at 1:19
Some explanation of this code and the occasional CR would help a lot here.
– scsimon
Jan 4 at 1:19
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54031590%2fsql-server-relationship-between-columns-in-different-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have you tried
INFORMATION_SCHEMA.KEY_COLUMN_USAGE?– Dai
Jan 4 at 0:24
Are you using any software to view tables? Some software like MS Access allow you to see relationships.
– Abu Nooh
Jan 4 at 0:28
Can you be sure that the designer of the database actually added foreign key constraints? Is there no design information regarding this database available?
– Dale Burrell
Jan 4 at 0:36
3
Possible duplicate of How to find foreign key dependencies in SQL Server?
– Vijunav Vastivch
Jan 4 at 0:42