Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
259 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

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…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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