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_identityOther 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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
ASKER
vs altering the constraint, I was just wondering if the SQL was working correctly.
You cannot alter constraint, only way is to drop and re-create. If I understand what you mean.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One concern is:
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.