Solved

Any way to find out where a column like a primary key has been ref in another table?

Posted on 2011-03-10
10
254 Views
Last Modified: 2012-08-13
Hi, I'm using sql 2005 standard edition.  I'm re-designing a database that I converted from MySql.  Is there a tool or a way to find all tables in this database that ref certain column of a table?  Well, no relationship existed when I converted the database.
In Visual Studio, I can select a variable and right click to find where it's ref/used.  that's kind of what I'm looking for.  Thank you
0
Comment
Question by:lapucca
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 35102480
Try something like this:


SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Open in new window


It should list all tables with primary keys
0
 

Author Comment

by:lapucca
ID: 35102606
This seems to give me a list of all primary keys in this database but it doesn't give me what tables are ref each of these key.  but appreciate the help anyway.  Maybe there is no way of doing this.
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 35104095
That's not true.

This code gives you indexname, table name and the primary key(s) associated with each table.

Try again and look at the column that says TableName.

When you run this query, you should see:

IndexName            TableName                   ColumnName

ColumnName is the primary key.

TableName is just what it says.

Isn't it what you are looking for?

0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 23

Expert Comment

by:wdosanjos
ID: 35104298
Try this:

USE YourDB
GO
SELECT C2.*
  FROM [Tracker].[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] RC
  INNER JOIN [Tracker].[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] C1 
     ON C1.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
        AND C1.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG 
        AND C1.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
  INNER JOIN [Tracker].[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE] C2 
     ON C2.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
        AND C2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG 
        AND C2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
  WHERE C1.TABLE_NAME = '<< your table >>' AND C1.COLUMN_NAME = '<< your pk column >>'

Open in new window

0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35104335
Sorry. Here it is again without my DB ref.  It lists all references of a particular Table / Column based on the FK constraints.

select c2.*
  from [information_schema].[referential_constraints] rc
  inner join [information_schema].[constraint_column_usage] c1 
     on c1.constraint_name = rc.unique_constraint_name 
        and c1.constraint_catalog = rc.unique_constraint_catalog 
        and c1.constraint_schema = rc.unique_constraint_schema
  inner join [information_schema].[constraint_column_usage] c2 
     on c2.constraint_name = rc.constraint_name
        and c2.constraint_catalog = rc.unique_constraint_catalog 
        and c2.constraint_schema = rc.unique_constraint_schema
  where c1.table_name = '<< your table >>' and c1.column_name = '<< your pk column >>'

Open in new window

0
 

Author Comment

by:lapucca
ID: 35111857
wd, That's very close to what I'm looking for.  I'm trying to set up relationship for database imported from mysql so it would be good if I don't have to manually examine and find tables that uses a FK.  Since there is no relationship set up so this script won't be able to find them.  
Is there any way to find tables that contain/ref the "name" of a PK column of another table?  thanks.
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 500 total points
ID: 35112725
Please try this.  It lists all columns where the name contains the name of a PK.  If you just want the ones that match exactly, change line cols.COLUMN_NAME like '%' + pkcols.COLUMN_NAME + '%' to say cols.COLUMN_NAME = pkcols.COLUMN_NAME.

select pkcols.TABLE_CATALOG PKTABLE_CATALOG
      ,pkcols.TABLE_SCHEMA PKTABLE_SCHEMA
      ,pkcols.TABLE_NAME PKTABLE_NAME
      ,pkcols.COLUMN_NAME PKCOLUMN_NAME
      ,cols.*
  from [INFORMATION_SCHEMA].[COLUMNS] cols
  inner join [INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] pk 
     on pk.CONSTRAINT_CATALOG = cols.TABLE_CATALOG
        and pk.CONSTRAINT_SCHEMA = cols.TABLE_SCHEMA
        and pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
  inner join [information_schema].[constraint_column_usage] pkcols
     on pkcols.CONSTRAINT_CATALOG = pk.CONSTRAINT_CATALOG
     and pkcols.CONSTRAINT_SCHEMA = pk.CONSTRAINT_SCHEMA
     and pkcols.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
  where cols.COLUMN_NAME like '%' + pkcols.COLUMN_NAME + '%'
    and (   cols.TABLE_CATALOG <> pkcols.TABLE_CATALOG
         OR cols.TABLE_SCHEMA <> pkcols.TABLE_SCHEMA
         OR cols.TABLE_NAME <> pkcols.TABLE_NAME)

Open in new window

0
 

Author Closing Comment

by:lapucca
ID: 35112819
Thanks wd. that's exactly what I'm looking for.   Where can I learn more about coding with INFORMATION_SCHEMA?  Thank you.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35112860
More about the INFORMATION_SCHEMA views:

http://msdn.microsoft.com/en-us/library/ms186778.aspx
0
 

Author Comment

by:lapucca
ID: 35112869
Thank you.
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question