Solved

detect foreign key?

Posted on 2013-01-29
6
242 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:knowlton
  • 3
  • 2
6 Comments
 
LVL 6

Assisted Solution

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

Author Comment

by: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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 5

Author Comment

by: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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

911 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

24 Experts available now in Live!

Get 1:1 Help Now