[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

detect foreign key?

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
Tom Knowlton
Asked:
Tom Knowlton
  • 3
  • 2
2 Solutions
 
liijaCommented:
0
 
Tom KnowltonWeb developerAuthor Commented:
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
 
liijaCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Tom KnowltonWeb developerAuthor Commented:
vs altering the constraint, I was just wondering if the SQL was working correctly.
0
 
liijaCommented:
You cannot alter constraint, only way is to drop and re-create. If I understand what you mean.
0
 
deviprasadgCommented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now