Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
261 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

877 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