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
202 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 28

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 28

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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now