SQL Server experts - I need your help!!

[Scott at Pelican Parts]

I have a database I am trying to make sense of. It has about 100 tables (all named in German) and each of those tables has plenty of fields (all named in German).

I need to figure out the relationships between all the fields/tables (assume the related fields share the same name among the tables).

Is there a piece of software out there that would analyze the field names and put together the relationships for me, ideally presenting them in a graphical format? I could do it manually, but that would take a week.

- Scott

[TheMentat]

Pretty sure Microsoft Visio will do it. If I recall, it doesn't "organize" the drawing well, and it might come out looking like spaghetti.

[TheMentat]

Originally Posted by TheMentat

Pretty sure Microsoft Visio will do it. If I recall, it doesn't "organize" the drawing well, and it might come out looking like spaghetti.

I should also mention, that I don't think it will establish relationships between fields unless there is an explicit foreign key relationship.

[id10t]

The tools from SQL 2k allow a visual representation (think it is in the enterprise manager thingie) , but its been forever since I've used MSSQL...

[Scott at Pelican Parts]

I should emphasize that these tables were imported into SQL Server from a text file or something, so there are no existing relationships that I'm aware of -- these are just raw tables. What I'm trying to do is determine the relationships, not just display them.

Thanks again.

[stomachmonkey]

Originally Posted by Scott at Pelican Parts

I should emphasize that these tables were imported into SQL Server from a text file or something, so there are no existing relationships that I'm aware of -- these are just raw tables. What I'm trying to do is determine the relationships, not just display them.

Thanks again.

If there are no existing relationships in the text file then there is nothing out there that is going to automagically figure it out.

[billwagnon]

To me, the German would suggest it's an SAP database. You might be able to find an SAP reference that would help you make sense of the table & field names.

[TheMentat]

Originally Posted by Scott at Pelican Parts

I should emphasize that these tables were imported into SQL Server from a text file or something, so there are no existing relationships that I'm aware of -- these are just raw tables. What I'm trying to do is determine the relationships, not just display them.

Thanks again.

yikes...

I can't think of anything off the shelf. But if I were to do it manually, I'd do it by running queries on the system tables for the DB. Find out which fieldnames occur more than once in the DB, and then find out which tables they occur in.

[legion]

DBA wanna-be here.

I'd think you'd have to know the data model to automate that kind of thing. If it were even possible to do what you are asking, all of the keys would have to be named the same in each table they are used. One deviation could throw everything off.

I don't know of any tool that you can feed a database and it will generate a data model. We use the Rational suite around here to do the opposite (use the data model to generate the database).

[TheMentat]

Try this:

select c.name as ColumnName
, o.name as TableName
, count(cc.name) as NumCols
from
sysobjects o
join syscolumns c
on c.id = o.id
and o.xtype = 'U'
left join syscolumns cc
on c.name = cc.name
join sysobjects co
on cc.id = co.id
and co.xtype = 'U'
group by c.name, o.name
order by NumCols desc, ColumnName, TableName



ignore all of the columns that have NumCols = 1

[TheMentat]

Originally Posted by legion

DBA wanna-be here.

I'd think you'd have to know the data model to automate that kind of thing. If it were even possible to do what you are asking, all of the keys would have to be named the same in each table they are used. One deviation could throw everything off.

I don't know of any tool that you can feed a database and it will generate a data model. We use the Rational suite around here to do the opposite (use the data model to generate the database).

I would second this... the code I provided will hopefully get you started on establishing relationships between the tables based on the column names only.


I should also say, that I'm not in any way a DB professional either... so my advice is probably only worth what it cost you!

[Scott at Pelican Parts]

Thanks guys. Mentat, that helped some!

[legion]

Mentat, it looks like you are using the LUW version of DB2 based on the column names you selected from the catalog.

[TheMentat]

Originally Posted by legion

Mentat, it looks like you are using the LUW version of DB2 based on the column names you selected from the catalog.

you've already lost me!

[rcecale]

All of you are looking at this from the wrong angle. The correct response is...



Just my own $.02.

Randy