How to find the unique / foreign keys in SQL Server Tables
Posted on 2015-01-13
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