• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

How to find the unique / foreign keys in SQL Server Tables

Hi All,

Thanks for all your support on EE.

I have rather a strange question and need your support on the same.

I have a SQL database with more than 100 tables.  All the tables have current data and the column names in all the tables are harmonized i.e if the column name is Internetsales in Table A in Table Z also the column name is Internetsales.

The point is we do not have any primary-key / Foreign keys listed in the database, my task is to go through each of the objects and find the same based on my understanding of underlying data.

I am looking forward to this group that is there a way to avoid that and do it auto-magically .

I know that if i do it manually , I would be able to do it more easily as i understand data, but prone for manual errors. But I am curious if the system can run those queries i can use that for this and many databases.. I am sure many you will agree this is a time saver.

The reasoning and example is

a) Primary Key. Records in Column A or set of columns (A,B,C) in this table X does not repeat in Table X.
b) Foreign key. Records in Column A or set of columns (A,B,C) in this table Y does repeats Table Y.
c) Primary Key. Records in Column A or set of columns (A,B,C) in this table Z does not repeat in Table Z.

combination a) to b) is a one to many
combination a) to c) is a one to one

Requirements of the sql script.

1 Based on the column name or set of columns i provide, it should loop through every object in the database that has those columns and find contained objects and eliminating one or any of these objects that don't have specified columns.

2. For those respective objects it should loop through / query the data table to find if it is unique in that table or not and list the results.

Thanks in advance for your help
Krishnan Subramanian
Krishnan Subramanian
1 Solution
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I do not believe there is any automated process, automagic or involving smoke or mirrors, that will interpret what the foreign keys are between tables without keys or indexes, and create them.

So ... you're on your own pal.
Krishnan SubramanianManagerAuthor Commented:
Thanks Jim , almost thought of that.

However my thinking was.

1) Run query against syscolumns and sys objects to find out the tables in which the specified columns appear
2) Loop a simple query on thoose individual tables by running distinct column name, count of column having count of column value > 2

I am sure this can be done since it is mechanical process.

The key is i am able to provide the initial set of combinations to check for.
Scott PletcherSenior DBACommented:
If the clustered indexes are done well, they will often be a natural key of some type.  Otoh, if the tables have the dopey "default" of identity as a clustering key, then they will be useless.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Krishnan SubramanianManagerAuthor Commented:
You are right. But I want the relationships to build business application.
You could conceivably loop thru every column of every table in the database to determine whether [based on the current content] it contains only unique values and as such could be a candidate for a PK.

You could also do similar process to determine whether a column looks 'like a' a foreign key of any of the columns of another table. (ie. contains only values found in the other table's column).

BUT, output of such analysis would be likely to contain 99.9 unhelpful content. There is no substitute for human examination of the data and whatever business logic, rules and documentation you can lay your hands on, including any legacy front-end applications. Column names and their position in the tables could also provide some clues.

Once you've looked at the table contents you'll probably have a good idea of the columns that are worth testing...
--Test for suitability as a PK (non-empty result set = FAIL)

--Test for suitability as an FK (non-empty result set = FAIL)
SELECT countryname FROM table1 WHERE countryname NOT IN (SELECT countryname FROM table2)

I'm curious about the context for this - are you trying to revive a dead legacy application or rebuild from a data dump?
Krishnan SubramanianManagerAuthor Commented:
Simon thanks for your insights. Yes I am trying my hands on db migration without proper documentation and support. Also no much help. This activity is consolidation of databases and centralization. I will go that route. Thought it is not foolproof it lays down first level of review and validation. I will accept this as solution for now
.would be glad if somebody can provide the actual so for looping and identification.
Krishnan SubramanianManagerAuthor Commented:
Thanks Simon
>would be glad if somebody can provide the actual so for looping and identification.
My point was that it is not a good idea to try to loop through every column doing this. You need to home in on the likely key columns first and then test them individually.

I've just remembered the Data Profiling Task in SSIS. You can use this to "help you become familiar with a data source". I haven't used it myself since learning about it during as SSIS course, but it would probably be very useful for your present challenge. Please take a look at the link..
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

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