Generate Table Relationship - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Generate Table Relationship

Author

Topic

spidyboy
Starting Member

1 Post

Posted - 2012-07-26 : 02:13:06

well i have seen the code

SELECT name ColumnName, OBJECT_NAME(c.object_ID) AS TableName
FROM sys.columns c
WHERE name IN
(
SELECT name FROM sys.columns
GROUP BY name having count(name) > 1
)
ORDER BY Name

but it only give result something like this

ColumnName TableName
deptid mstEmployee
deptid mstDepartment
DesignationID mstEmployee
DesignationID mstDesignation

can i have the result something like

mstEmployee 1..* mstDepartment

mstEmployee 1..1 mstDesignation

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-26 : 06:08:06

Sort of.
Would have to implement the relationships using foreign keys.

mstEmployee 1..* mstDepartment
would be a foreign key on a nullable not-unique column on mstDepartment referencing mstEmployee
That would give you the ..* bit.
The 0,1.. would be via a unique index on the columns in mstEmployee
Then you have to get the covering 1.. part of the relationship.
That would probably be implemented via a trigger which makes things difficult as you would have to parse it.

Another option is to always implement relationships via a conjoint table (table for relationship).
That might make it easier.

Probably a better option would be to create a spreadsheet to hold your database design.
(I usually create my databases like this as it gives consistent coding).
This could have these relationships indicated and the database schema created from it.
You don't actually have to create the database from this - just have a process to compare with what is there.

Once you are doing that you might think about using a product like ErWin.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources