Old crazy database

Hi! Have you ever wanted to make life easier? Well, so I am :-)

I have to understand old database ported to ms sql server 2000. Well, the problem is that there are no primary keys at all, no constraints. Database logic is thrown onto triggers and Delphi applications. All fields that should be primary key are in people heads...just mentally :(

Well, you'll say try to find equal fields in different databases... well i was trying to do this. It's impossible...

Any ideas except social engineering?

ThanxI don't know if the premise of this whole exercise is valid, but I thought I would give it a try.

The procedure below will generate a list of columns with unique rows that match the number of rows in the table for all tables in the database. That should help you find possible primary keys. It will not be able to identify composite keys, but it may be a starting point.

Notice the database name has to be changed to your database in a couple of places. It dynamically creates a view, will be painfully slow if you have a lot of data, requires system admin rights and <disclaimer here>.

USE Pubs

Create Procedure p_FindKeys (@.TableName sysname)
AS
BEGIN
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF

Print @.TableName

IF Object_ID('v_FindKeys') IS Not Null
DROP VIEW v_FindKeys

IF Object_ID('tempdb..##exec') IS Not Null
DROP TABLE ##exec

SELECT ID = Identity(int, 1,1),
Cmd = Cast ('Create View v_FindKeys AS ' AS Varchar(255))
INTO ##exec

INSERT ##exec
SELECT 'Select Count(Distinct['+COLUMN_NAME+']) RowsCnt, ''['+COLUMN_NAME+']'' ColName FROM ['+TABLE_NAME+'] UNION ALL ' Cmd
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @.TableName AND DATA_TYPE not in ('ntext','image','text')

INSERT ##exec (cmd) SELECT 'SELECT count(1) rowcnt, ''#['+@.TableName+']#'' ColName FROM ['+@.TableName+']'

EXEC master..xp_execresultset N'SELECT ''''+Cmd+'''' FROM ##exec ORDER BY ID ',N'Pubs'
DROP TABLE ##exec

SELECT 'Possible Primary Key for table:['+@.TableName+'] -->' + ColName + '(' + Cast(RowsCnt AS Varchar) + ' Rows )' Candidates
FROM v_FindKeys WHERE RowsCnt = (SELECT RowsCnt From v_FindKeys WHERE ColName = '#['+@.TableName+']#')
AND ColName <> '#['+@.TableName+']#'
END

Run:

master..xp_execresultset 'SELECT ''exec p_findkeys '''+TABLE_NAME+''' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''',N'Pubs'

To generate output similar to:

Categories
Candidates
---------------------
Possible Primary Key for table:[Categories] -->[CategoryID](8 Rows )
Possible Primary Key for table:[Categories] -->[CategoryName](8 Rows )

clientmaster
Candidates
----------------------
Possible Primary Key for table:[clientmaster] -->[LastPayment](3 Rows )
Possible Primary Key for table:[clientmaster] -->[IntStatus](3 Rows )

CustomerCustomerDemo
Candidates
------------------------
Possible Primary Key for table:[CustomerCustomerDemo] -->[CustomerID](0 Rows )
Possible Primary Key for table:[CustomerCustomerDemo] -->[CustomerTypeID](0 Rows )

CustomerDemographics
Candidates
-------------------------
Possible Primary Key for table:[CustomerDemographics] -->[CustomerTypeID](0 Rows )

Customers
Candidates
---------------------
Possible Primary Key for table:[Customers] -->[CustomerID](91 Rows )
Possible Primary Key for table:[Customers] -->[CompanyName](91 Rows )
Possible Primary Key for table:[Customers] -->[ContactName](91 Rows )
Possible Primary Key for table:[Customers] -->[Address](91 Rows )
Possible Primary Key for table:[Customers] -->[Phone](91 Rows )

Employees
Candidates
--------------------
Possible Primary Key for table:[Employees] -->[EmployeeID](9 Rows )
Possible Primary Key for table:[Employees] -->[LastName](9 Rows )
Possible Primary Key for table:[Employees] -->[FirstName](9 Rows )
Possible Primary Key for table:[Employees] -->[BirthDate](9 Rows )
Possible Primary Key for table:[Employees] -->[Address](9 Rows )
Possible Primary Key for table:[Employees] -->[PostalCode](9 Rows )
Possible Primary Key for table:[Employees] -->[HomePhone](9 Rows )
Possible Primary Key for table:[Employees] -->[Extension](9 Rows )

EmployeeTerritories
Candidates
------------------------
Possible Primary Key for table:[EmployeeTerritories] -->[TerritoryID](49 Rows )

Orders
Candidates
-------------------
Possible Primary Key for table:[Orders] -->[OrderID](830 Rows )

Products
Candidates
--------------------
Possible Primary Key for table:[Products] -->[ProductID](77 Rows )
Possible Primary Key for table:[Products] -->[ProductName](77 Rows )

Region
Candidates
-------------------
Possible Primary Key for table:[Region] -->[RegionID](4 Rows )
Possible Primary Key for table:[Region] -->[RegionDescription](4 Rows )

Shippers
Candidates
--------------------
Possible Primary Key for table:[Shippers] -->[ShipperID](3 Rows )
Possible Primary Key for table:[Shippers] -->[CompanyName](3 Rows )
Possible Primary Key for table:[Shippers] -->[Phone](3 Rows )

Suppliers
Candidates
---------------------
Possible Primary Key for table:[Suppliers] -->[SupplierID](29 Rows )
Possible Primary Key for table:[Suppliers] -->[CompanyName](29 Rows )
Possible Primary Key for table:[Suppliers] -->[ContactName](29 Rows )
Possible Primary Key for table:[Suppliers] -->[Address](29 Rows )
Possible Primary Key for table:[Suppliers] -->[City](29 Rows )
Possible Primary Key for table:[Suppliers] -->[PostalCode](29 Rows )
Possible Primary Key for table:[Suppliers] -->[Phone](29 Rows )

Territories
Candidates
----------------------
Possible Primary Key for table:[Territories] -->[TerritoryID](53 Rows )

Or run

SELECT 'exec p_findkeys '''+TABLE_NAME+'''' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

To get script you can run manually on each table:

exec p_findkeys 'authors'
exec p_findkeys 'discounts'
exec p_findkeys 'employee'
exec p_findkeys 'f_authors'
exec p_findkeys 'jobs'
exec p_findkeys 'Numbers'
exec p_findkeys 'pub_info'
exec p_findkeys 'publishers'
exec p_findkeys 'roysched'
exec p_findkeys 'sales'
exec p_findkeys 'stores'
exec p_findkeys 'titleauthor'
exec p_findkeys 'titles'|||OK, if there are no PK/FK's, are there any indexes? I know that erwin will make a good guess and may come up pretty close if a situation like this is presented.|||Well, thanx for ERWIN idea ;-)
Yes, there are indexes...

I do not have administrative rights for the moment, so I tryed to rewrite vaxman's script