Database Woes

scojones

Hi,
I have a gigantic Microsoft SQL Server database, with hundreds of databases, with hundreds of tables, each having roughly 70 fields. The beauty of this is that there is no database schema.. So is there a quick way that I can see which fields have relationships? Is there some way that I can reverse engineer a schema perhaps? If I am to do this by hand it could take months.

Thanks alot in advance.
sj.

bonkey

Ummm...I'm not sure what you mean by there being no database schema. You can tell SQL Server Enterprise Manager (MSSQL 7.0 and newer) to create a database diagram for you, and voila...one instant schema.

However...if you want to do stuff programmatically...

have a look at sysreferences, which contains details about the foreign key relationships (i.e. exactly what you're looking for).

The Ids in here should join to fields in syscolumns (where fields - aka columns - are defined).

These, in turn will have tableIds (or somesuch) which link to the sysobjects table ( where all objects - such as tables) are defined.

Note that this approach will only give you the explicitly defined foreign key relationships (FKRs). It will not give you any relational connection defined using other techniques (e.g. 'hand-rolled' triggers), nor will it give you anything where a relationship exists logically but isn't enforced on the database.

Its as good as yer gonna get tho, I reckon.

If you need any more info....let me know.

scojones

Sorry bonkey I should have been more clear in my post. Basically we have a huge db, and no documentation about it. How can I tell SQL Server Enterprise Manager (MSSQL 7.0 and newer) to create a database diagram for me?

Thanks,
sj.

scojones

Ah I've created the diagram of one of the databases. Ok that's great. Thank you. Now I just have to do this for the few hundred databases that are there. Basically I have to create an ERD of this database. This looks like it's going to be pretty hard considering the size.

bonkey

OK...if you need to do it for *every* database on the server, and each is different, then diagrams are going to take forever.

If taxt-based output is enough, your best bet is to knock up a query for one database to produce the output you want, and then wrap it (most probably in a cursor as shown below) with some code which will cause the thing to iterate across each database on the server producing the same output.

You might find that what you want is something like the following...which coudl easily be fleshed out...the unfinished query in the middle is the one I described earlier. The rest should work, other than syntax errors (I've no MSSQL server at hand, so I'm going from memory).

declare @db_name varchar(255)
declare @use_string varchar(259)
 
create cursor mycursor as 
select distinct name from master..sysdatabases
 
open mycursor
fetch next from mycursor into @db_name 
while @@fetch_status = 1 -- or whatever indicates success
use_string = 'use ' + @db_name
exec(use_string)
 
select @db_name,
-- referencing column + table goes here
-- referenced column + table goes here
from sysreferences
-- join syscolumns twice - once for the referencing, once for teh referenced 
-- join sysobjects twice - once for each syscolumns table
...
 
fetch next from mycursor into @db_name 
loop
close mycursor
deallocate mycursor

There is another cursor syntax also supported, but this is hte most compatible across versions.

scojones

That's great bonkey. Thanks alot!

bonkey

Welcome.

amen

sjones
I have a little utility written in c# that documents sql database producing
table/sp/view structrures, their dependancies and index/keys.
Would this be of any use to you ?
I'm looking a beta tester

let me know if you are interested