Solved

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

Posted on 2015-01-13
8
210 Views
Last Modified: 2015-01-13
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
0
Comment
Question by:Krishnan Subramanian
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40547392
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.
0
 

Author Comment

by:Krishnan Subramanian
ID: 40547422
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40547448
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.
0
 

Author Comment

by:Krishnan Subramanian
ID: 40547500
You are right. But I want the relationships to build business application.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 18

Accepted Solution

by:
SimonAdept earned 500 total points
ID: 40547515
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...
 e.g.
--Test for suitability as a PK (non-empty result set = FAIL)
SELECT id FROM table1 GROUP BY id HAVING COUNT(id)>1

--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?
0
 

Author Comment

by:Krishnan Subramanian
ID: 40547533
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.
0
 

Author Closing Comment

by:Krishnan Subramanian
ID: 40547536
Thanks Simon
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40547600
>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..
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now