Solved

detect foreign key?

Posted on 2013-01-29
6
247 Views
Last Modified: 2013-01-30
SELECT t.name as tablename, c.name as columnname, st.name, c.is_identity, c.is_nullable
--,c.max_length, c.precision, c.scale
FROM sys.tables t
   JOIN sys.columns c on t.object_id = c.object_id
   join sys.types st on c.system_type_id = st.system_type_id
WHERE t.type='U'
--and c.name like '%type%'
ORDER BY t.name, c.column_id, c.is_identity

Open in new window


Other experts helped me piece together this SQL to show all tables and their columns for a database.  Every so often I tweak it.


how might I show if a column is a foreign key?

are constraints recorded in the sys tables?
0
Comment
Question by:Tom Knowlton
[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
  • 3
  • 2
6 Comments
 
LVL 6

Assisted Solution

by:liija
liija earned 150 total points
ID: 38832241
0
 
LVL 5

Author Comment

by:Tom Knowlton
ID: 38832311
The link is very very good, and it works.


One concern is:

pkfk
Line 12 is confusing as is line 13.

I interpret it like this:

"Table PhotoLog has a column named UpdatedByID which is a foreign key.  This foreign key is a column in table UserID named UserSID"

This is not accurate.

SID stands for server id.  and the columns are always mapped SID = SID between two tables.

Lines 11 and 14 accurately depict the PK / FK relationships between table PhotoLog and table User.

how can this be corrected?

I assume it is some sort of join that is to blame, but not the best at T-SQL.
0
 
LVL 6

Expert Comment

by:liija
ID: 38832479
If you are sure what you are doing run:
ALTER TABLE PhotoLog DROP CONSTRAINT FK_PhotoLog_UpdatedByID

and recreate the correct one (not sure what you need, perhaps one for ID and other for SID?)
ALTER TABLE Photolog
ADD CONSTRAINT FK_Photolog_UpdatedBy_ID FOREIGN KEY(UserID)REFERENCES User(UserID)
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 5

Author Comment

by:Tom Knowlton
ID: 38832621
vs altering the constraint, I was just wondering if the SQL was working correctly.
0
 
LVL 6

Expert Comment

by:liija
ID: 38832644
You cannot alter constraint, only way is to drop and re-create. If I understand what you mean.
0
 
LVL 10

Accepted Solution

by:
deviprasadg earned 350 total points
ID: 38834348
Please check this query:
SELECT DISTINCT t.name as tablename, c.name as columnname, st.name, c.is_identity, c.is_nullable, CASE WHEN PT.PK_Table IS NULL THEN 0 ELSE 1 END AS IsFK
--,c.max_length, c.precision, c.scale
FROM sys.tables t
   JOIN sys.columns c on t.object_id = c.object_id
   join sys.types st on c.system_type_id = st.system_type_id
--and c.name like '%type%'
LEFT JOIN (SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME) PT
ON PT.FK_Column = c.name
AND PT.K_Table = t.name
WHERE t.type='U'
--ORDER BY t.name, c.column_id, c.is_identity

Open in new window


Refer: http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

623 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