locked
how to see all foreign keys in database backup RRS feed

  • Question

  • Hello everyone!

    I have backed up a database from the producction server to my local machine, just for training, but I can't see all foreign keys in the database, I have run this query:

    SELECT f.name AS ForeignKey,
    OBJECT_NAME(f.parent_object_id) AS TableName,
    COL_NAME(fc.parent_object_id,
    fc.parent_column_id) AS ColumnName,
    OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
    COL_NAME(fc.referenced_object_id,
    fc.referenced_column_id) AS ReferenceColumnName
    FROM sys.foreign_keys AS f
    INNER JOIN sys.foreign_key_columns AS fc
    ON f.OBJECT_ID = fc.constraint_object_id
    

    But I just can see a few of tables with FK, and I'm sure there's a lot of more, I think so it's due to permissions, but I'm logging in as "SA", how I can give to this user full access to see all foreign keys??

    Thank you

    Thursday, August 19, 2010 6:45 PM

Answers

  • Since you are logged in as 'sa' it should not be a rights issue.  Did you mean the 'sa' login, or that you have sysadmin rights?  If the following code returns a 1 you are not having a rights issue:

     SELECT IS_SRVROLEMEMBER('sysadmin')

    The sys.foreign_keys view is defined as "Contains a row per object that is a FOREIGN KEY constraint, with sys.object.type = F." 

    However, it is quite possible to use columns as foreign keys without declaring the constraint. I suspect that is the case in your database.  In that case, the column may be named for the primary key of another table or may have an index on it that was named 'FKsomethingorother.'   These might be useful clues, although certainly less clear than if the constraint was defined.

    If you still have doubts, you could script out the database and then could the FOREIGN KEY constraints in the script.

    FWIW,
    RLF

     

    • Marked as answer by Ivanzinho Friday, August 20, 2010 6:25 PM
    Thursday, August 19, 2010 8:21 PM