Primary Key

Hello all,
I'm taking over a project from another developer and i've run into a bit of a problem.

This developer had a bad habit of not using primary keys when designing various databases used by his programs. So now i've got approx 1000 tables all of which do not have primary keys assigned.

Does anyone know of a tsql script that i can run that will loop through each table and add a primary key field?

Thanks in advance?

Richard M.

It sounds temping, but you need to understand several things.

What is a primary key on a table, and what is an index. You might need a complex key (say 3 fields), but you add an "autonumber" field to propgate as a foreighn key. Then what about indexing other fields, because without it, a database can become bogged down as data grows.

You are a brave man to take this one on. I would suggest requesting time to rationalise what is there, especially with so many tables. The risks of failure in your project is increased if you do not review what is there.

It is however, possible to add indexes. Look at the system tables, from which you can get the names of all user tables - you can do this in VB or C# ny calling a stored proc to get the tables names. Then simply execute some SQL on a connection to run the same script to add an ID column - there are plenty of examples of adding fields in SQL in the SQL Server help.

Good luck

|||

The good news is you can ALTER your tables manually in Enterprise Manager and generate the script to create the new table in a new version of your database. The bad news is the tool is very expensive, the cheap one is $400 called AdeptSQL but you can test drive Embarcadero for 14 days. Try the link below for details. 1000 tables makes it worth looking into tools so you don't make expensive mistakes. Hope this helps.

http://www.msdner.com/forum/thread78800.html

http://www.embarcadero.com/products/dbartisan/index.html