Solved

detect foreign key?

Posted on 2013-01-29
6
246 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

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