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
248 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

734 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