September 28, 2009 at 12:58 pm
Hi Guys,
I found a bunch of SQL databases without a diagram, I am not aware on how they were built, I am not able either to get it from a reverse engineering, since the tables does not have any FK..just PK....I am trying to using TOAD but is not giving me any help...any suggestion please?...may be I am missing something by running Toad, or may be there is another option to use it and get what I am loking for...I need to have the ERD because I was asked to add columns, but how I will if I not know where is the relationship...
Thanks in advance.
September 28, 2009 at 1:39 pm
Unfortunately, if there are no FK's defined then there are no relationships, at least as far as the db is concerned, so and ERD with no relations is correct. You can't reverse engineer something that just plain isn't there.
You'll need to take a look at the code and do a manual review to see where you are joining which tables. I supposed you could take a trace and review the most common queries to see what tables are involved and what joins are used. This could at the very least give you some idea of how it all fits together.
Then I'd make sure you have a good ERD or Data dictionary that's kept up to date from here on out.
-Luke.
September 28, 2009 at 1:59 pm
Chances are the Db integrity was designed on the front end...so you dont have anyone to ask I would try the developers....I have a number of DB that dont have any relations. This generally happens for 2 reason. 1. a DBA was not hired at the beg. and a developer just created a bunch of tables...usually lots 2nd normal form table sometime 1st normal form. Or #2 they hired a DBA with not enough experience and he didnt know enough to fight with the devlopers on DB Design.
Good LUCK you work is cut out for you
September 29, 2009 at 4:39 am
I was in a similar situation. Their were no FKs but the
PKs and FKs had a naming convention somewhat like
parenttable : ID PK
Child1: parenttableID FK
Child2: parenttableID FK
It took a bit of research to get to the nomenclature.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 30, 2009 at 3:07 am
I can only confirm what all others have said already. I had a similar situation where the relations between the tables not even could be guessed by the field names. Finally I "reverse engineered" everything into Microsoft Visio and started to look at the application code. Specially the SQL statements sometime helped to reveal relationships. After that exercise I had a few sessions with somebody who understands the business very well and we reviewed my findings and made adjustments to the documentation where necessary. Along with that I started to build a data dictionary.
It's the hard way but you'll learn a lot about the application and the business.
Good luck;-)
September 30, 2009 at 5:37 am
I had also tripped over the same issue and if the following assumptions are true, this script might help:
the first column of the table is a sinlge column PK, identity or not.
the first column has a naming convention, ending in "ID" or "tblkey" or something
the FK columns have the same column name as the pk, ie parent table has "ParentID", and the FK child table is the same name : "ParentID"
if the design has those features, this script will identify and create the ALTER TABLE scripts to put the missing FK's in place...it jjust generates a list for approval, but it helped me out in the past:
--Missing_And_Implied_Foreign_Keys.sql
if object_id('tempdb..#FKFINDER') Is not null
drop table #FKFINDER
if object_id('tempdb..#CurrFKS') Is not null
drop table #CurrFKS
SELECT
CONVERT(VARCHAR(500),'') AS STATUS ,
SYSOBJECTS.NAME AS TBLNAME,
SYSOBJECTS.ID AS TBLID,
SYSCOLUMNS.NAME AS COLNAME,
TYPE_NAME(SYSCOLUMNS.XTYPE) AS DATATYPE
INTO #FKFINDER
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID=SYSCOLUMNS.ID
WHERE SYSOBJECTS.XTYPE='U'
--tables we know we can exclude
AND SYSOBJECTS.NAME NOT IN('CMCONT','CMMLADDR','CMADDR')
AND TYPE_NAME(SYSCOLUMNS.XTYPE)='INT'
AND SYSCOLUMNS.NAME LIKE '%TBLKEY' --or %ID or some convention?
ORDER BY TBLNAME,COLNAME
SELECT
OBJECT_NAME(constid) AS CONSTRAINTNAME,
OBJECT_NAME(rkeyid) AS REFTABLE,
COL_NAME(rkeyid,rkey) AS REFCOLUMN,
OBJECT_NAME(fkeyid) AS FKTABLE,
COL_NAME(fkeyid,fkey) AS FKCOLUMN,
' ALTER TABLE ' + OBJECT_NAME(fkeyid)
+ ' ADD CONSTRAINT ' + OBJECT_NAME(constid)
+ ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey)
+ ') REFERENCES ' + OBJECT_NAME(rkeyid)
+'(' + COL_NAME(rkeyid,rkey) + ')' AS FKSQL
Into #CurrFKS
from sysforeignkeys
--mark all the PK's
UPDATE #FKFINDER
SET STATUS = 'PK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.REFTABLE
AND #FKFINDER.COLNAME = #CURRFKS.REFCOLUMN
UPDATE #FKFINDER
SET STATUS = 'PK'
FROM(
SELECT OBJECT_NAME(ID) AS PKTABLE,
NAME as PKCOLUMN,
* FROM SYSCOLUMNS
WHERE SYSCOLUMNS.NAME LIKE '%TBLKEY' --or %ID or some convention?
AND COLID=1) X
WHERE #FKFINDER.TBLNAME = X.PKTABLE
AND #FKFINDER.COLNAME = X.PKCOLUMN
--mark all the curr FK's
UPDATE #FKFINDER
SET STATUS = 'FK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.FKTABLE
AND #FKFINDER.COLNAME = #CURRFKS.FKCOLUMN
--fix specific to mail columns
UPDATE #FKFINDER
SET STATUS = 'FK'
FROM #CURRFKS
WHERE #FKFINDER.TBLNAME = #CURRFKS.FKTABLE
AND #FKFINDER.COLNAME = #CURRFKS.FKCOLUMN
--here's a best guess as to what it might point to:
UPDATE #FKFINDER
--SET STATUS=X.TBLNAME + '.' + X.COLNAME
SET STATUS = 'ALTER TABLE ' + #FKFINDER.TBLNAME + ' ADD FOREIGN KEY (' + #FKFINDER.COLNAME + ') REFERENCES ' + X.TBLNAME + '(' + X.COLNAME + ')'
FROM (SELECT * FROM #FKFINDER WHERE STATUS='PK') X
WHERE #FKFINDER.STATUS NOT IN('PK','FK')
AND #FKFINDER.COLNAME = X.COLNAME
--fix against columns starting with 'MAIL'
UPDATE #FKFINDER
--SET STATUS=X.TBLNAME + '.' + X.COLNAME
SET STATUS = 'ALTER TABLE ' + #FKFINDER.TBLNAME + ' ADD FOREIGN KEY (' + #FKFINDER.COLNAME + ') REFERENCES ' + X.TBLNAME + '(' + X.COLNAME + ')'
FROM (SELECT * FROM #FKFINDER WHERE STATUS='PK') X
WHERE #FKFINDER.STATUS NOT IN('PK','FK')
AND REPLACE(#FKFINDER.COLNAME,'MAIL','') = X.COLNAME
--this is a list of all columns that end in "TBLKEY", which implies a FK link according to our schema, but does nto have a FK using the table/column.
--not definitive, but a good guess/good start need to examine carefully
SELECT *
FROM #FKFINDER
WHERE STATUS NOT IN('PK','FK')
AND STATUS LIKE 'ALTER%'
--and status like '%GMPROJ%' --testing for items for a specific table i THINK had missing FK
ORDER BY TBLNAME,COLNAME
Lowell
September 30, 2009 at 6:07 am
September 30, 2009 at 2:31 pm
Check out this other topic: Reverse Engineer Huge Database (Table...
at http://www.sqlservercentral.com/Forums/Topic773121-146-1.aspx
They have much the same problems as you. Several tools were discussed, such as Embarcadero's ER/Studio, that will try to infer primary and foreign keys when they don't explicitly exist.
David Lathrop
DBA
WA Dept of Health
October 1, 2009 at 5:53 am
There can be a lot of trial-and-error involved in reverse engineering a database. I ran into a similar problem, trying to push customer data from a membership app into Microsoft Retail Management System. The RMS database is only tables, so there is no SQL to look at, and the columns are arranged in alphabetical order so no hints as to PK's. I took backups & restored to a DEV system, then studied the tables & built some queries, then compared my results to the RMS screens & reports to test my assumptions about the relationships. Good luck.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply