Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unanswered: 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?

    Thanx

  2. #2
    Join Date
    Jul 2004
    Posts
    52
    I 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'

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2004
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •