s********e
发帖数: 893 | 1 一个常见的问题是做一个新的复杂的数据库项目时，很多个table你不知道他们之间都
是什么关系。通过看每个table的constraints和dependecy，可以看到一个table的所有
FK都是reference哪个table的。我目前知道的在Oracle Sql Developer或Sq Server
management studio里都可以这样看到，但是只能一个table一个table的看。我想肯定
有一个tool，可以把这很多个table的关系自动生成一个ERD表，比如Table A的PK跟
Table B的FK自动连起来。请问Sql developer或sql server里都可以做吗？多谢了！ | n***l
发帖数: 143 | 2 In SSMS, click on the database, then database diagrams, choose create new
database diagram. Add the table of interest, then right click on that table
to add the related tables.
My question is how to quickly figure out the relationships if the database
has over 5000 tables? | H*********e
发帖数: 276 | 3 在任何一个database 下， 用这个code 找， 我以前工作里面一个大牛给我的
select
pt.[name] as [ParentTable],
pc.[name] as [ParentColumn],
ct.[name] as [ChildTable],
cc.[name] as [ChildColumn]
from sys.foreign_key_columns fk
JOIN sys.columns pc on pc.column_id = fk.parent_column_id and pc.object_id =
fk.parent_object_id
JOIN sys.columns cc on cc.column_id = fk.referenced_column_id and cc.object
_id = fk.referenced_object_id
JOIN sys.objects pt on pt.object_id = pc.object_id
JOIN sys.objects ct on ct.object_id = cc.object_id
-----MetaLayer:
select
t.[name] as [TableName],
c.[name] as [ColumnName],
c.is_identity as [IsPk],
k.type as [ContraintType],
case when tFK.[name] is not null and cFk.[name] is not null then 1
else 0 end as [IsFk],
c.[is_nullable] as [IsNullable],
y.[name] as [DataType],
tFK.[name] + '.' +
cFk.[name] as [FkName],
c.max_length as [MaxLength],
c.precision as [ColumnPrecision],
c.scale as [ColumnScale],
def.definition as [DefaultValue]
from sys.objects t
join sys.columns c on c.object_id = t.object_id
join sys.types y on y.system_type_id = c.system_type_id
left outer join sys.foreign_key_columns fk on fk.parent_column_id = c.column
_id and fk.parent_object_id = c.object_id
left outer join sys.columns cFk on cFk.column_id = fk.referenced_column_id
and cFk.object_id = fk.referenced_object_id
left outer join sys.objects tFK on tFK.object_id = cFk.object_id
left outer join sys.default_constraints def on def.object_id = c.default_
object_id
left outer join sys.key_constraints k on k.parent_object_id = c.object_id
and k.unique_index_id = c.column_id
where t.type = 'U' and y.[name]!= 'sysname'; | m******u
发帖数: 12400 | 4 thanks for sharing.
发信人: HoneyCoffee (贝贝), 信区: Database
标 题: Re: 给一堆table，怎样能自动生成ERD
发信站: BBS 未名空间站 (Sun Oct 11 22:24:08 2015, 美东)
在任何一个database 下， 用这个code 找， 我以前工作里面一个大牛给我的
select
pt.[name] as [ParentTable],
pc.[name] as [ParentColumn],
ct.[name] as [ChildTable],
cc.[name] as [ChildColumn]
from sys.foreign_key_columns fk
JOIN sys.columns pc on pc.column_id = fk.parent_column_id and pc.object_id =
fk.parent_object_id
JOIN sys.columns cc on cc.column_id = fk.referenced_column_id and cc.object
_id = fk.referenced_object_id
JOIN sys.objects pt on pt.object_id = pc.object_id
JOIN sys.objects ct on ct.object_id = cc.object_id
-----MetaLayer:
select
t.[name] as [TableName],
c.[name] as [ColumnName],
c.is_identity as [IsPk],
k.type as [ContraintType],
case when tFK.[name] is not null and cFk.[name] is not null then 1
else 0 end as [IsFk],
c.[is_nullable] as [IsNullable],
y.[name] as [DataType],
tFK.[name] + '.' +
cFk.[name] as [FkName],
c.max_length as [MaxLength],
c.precision as [ColumnPrecision],
c.scale as [ColumnScale],
def.definition as [DefaultValue]
from sys.objects t
join sys.columns c on c.object_id = t.object_id
join sys.types y on y.system_type_id = c.system_type_id
left outer join sys.foreign_key_columns fk on fk.parent_column_id = c.column
_id and fk.parent_object_id = c.object_id
left outer join sys.columns cFk on cFk.column_id = fk.referenced_column_id
and cFk.object_id = fk.referenced_object_id
left outer join sys.objects tFK on tFK.object_id = cFk.object_id
left outer join sys.default_constraints def on def.object_id = c.default_
object_id
left outer join sys.key_constraints k on k.parent_object_id = c.object_id
and k.unique_index_id = c.column_id
where t.type = 'U' and y.[name]!= 'sysname'; | s**********o
发帖数: 14359 | 5 VISIO PREMIER可以直接REVERSE ENIGNEERING |
|
