Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: Primary Key Analysis Tool

    I have a client database where the users are experiencing very unpredictable behavior and I found after investigating that the majority of the tables don't have primary keys.

    They were imported from an Access database and I think the table creator or the Access wizard itself added auto-number fields, so I know most of the tables have at least one column that could become the primary key.

    Is there a free tool available that will allow me to analyze these tables to identify and list the tables that need primary keys and also help identify candidate columns for the primary key?

    It would be helpful if I could automate some of the script generation to install the primary keys so I don't have to do them all manually.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you should be able to work out a query using the information schema views.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is it a full moon toay?

    Script out the database and look it over

    Or do as Sean said and left join tables key_column_usage
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Mar 2003
    Posts
    130
    Quote Originally Posted by Brett Kaiser
    Is it a full moon toay?

    Script out the database and look it over

    Or do as Sean said and left join tables key_column_usage
    No, sorry the moon was full on the 26th.

    The reason I asked about a tool to help with this is I don't have time or patience to script and analyze about 500 tables. The key_column_usage view isn't helpful since none of the user tables have keys - there are only two entries for the master table keys.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This script is beta, but it works pretty well. Supply a tablename and it will find all the natural keys in the data.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I didn't open the blind dudes script, but I guess it's looking for cardiality of the data.

    The more unique it is would be the key

    But you could write your own

    So lets' you find a column that all has unique values, I would use that as a key

    But what if you have columns with 2 or 3 values?

    Or mostly unique?

    Was that app written to suppose to enforce RI

    A lot of 3rd party apps I see do that

    scrubs
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2003
    Posts
    130
    Quote Originally Posted by blindman
    This script is beta, but it works pretty well. Supply a tablename and it will find all the natural keys in the data.
    Exactly what I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •