I have just inherited a SQL Server 2005 application with 200+ tables, none of which have indexes or primary keys, but they all have data in them. I wanted to see if anyone has a script or stored procedure that could identify suggestions for natural primary keys on the tables based on this criteria.
If you can perform a SELECT Count(*) FROM TABLE (or get the record count for the table in another way) and that equals the record count of a SELECT DISTINCT [TestColumn1], [TestColum2] FROM TABLE then the columns used in the SELECT DISTINCT combination were good candidates for a natural key.
It seems that the procedure would need to loop through all the columns in a table trying to find a combination of columns that yields the same record count as a SELECT * from the table. In the tables sometimes have 20+ columns you can see how this could be an issue without some kind of way to automate the suggestion of natural primary keys.
Any help would greatly greatly be appreciated.