怎样使数据库反向生成该数据库的表关系

imelodious 2009-01-22 12:02:50

我现在有个数据库,我怎样去找到他们之间相互的关系, 有没有哪个软件可以将数据库直接生成一个表之间的关系设计图之类的.
原来只用过pd去设计设计数据库,但不知道怎样反向操作.
(注:数据库自身带的关系图没有)

...全文

190 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

nalnait 2009-01-22

打赏
举报

pd 就可以

dawugui 2009-01-22

打赏
举报

在 SQL Server 2005 中查询表结构及索引 



-- 1. 表结构信息查询 



-- ========================================================================

-- 表结构信息查询



-- 邹建 2005.08(引用请保留此信息)

-- ========================================================================

SELECT 

    TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,

    TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),

    Column_id=C.column_id,

    ColumnName=C.name,

    PrimaryKey=ISNULL(IDX.PrimaryKey,N''),

    [IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,

    Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,

    Type=T.name,

    Length=C.max_length,

    Precision=C.precision,

    Scale=C.scale,

    NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,

    [Default]=ISNULL(D.definition,N''),

    ColumnDesc=ISNULL(PFD.[value],N''),

    IndexName=ISNULL(IDX.IndexName,N''),

    IndexSort=ISNULL(IDX.Sort,N''),

    Create_Date=O.Create_Date,

    Modify_Date=O.Modify_date

FROM sys.columns C

    INNER JOIN sys.objects O

        ON C.[object_id]=O.[object_id]

            AND O.type='U'

            AND O.is_ms_shipped=0

    INNER JOIN sys.types T

        ON C.user_type_id=T.user_type_id

    LEFT JOIN sys.default_constraints D

        ON C.[object_id]=D.parent_object_id

            AND C.column_id=D.parent_column_id

            AND C.default_object_id=D.[object_id]

    LEFT JOIN sys.extended_properties PFD

        ON PFD.class=1 

            AND C.[object_id]=PFD.major_id 

            AND C.column_id=PFD.minor_id

--             AND PFD.name='Caption'  -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)

    LEFT JOIN sys.extended_properties PTB

        ON PTB.class=1 

            AND PTB.minor_id=0 

            AND C.[object_id]=PTB.major_id

--             AND PFD.name='Caption'  -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) 



    LEFT JOIN                       -- 索引及主键信息

    (

        SELECT 

            IDXC.[object_id],

            IDXC.column_id,

            Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')

                WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,

            PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,

            IndexName=IDX.Name

        FROM sys.indexes IDX

        INNER JOIN sys.index_columns IDXC

            ON IDX.[object_id]=IDXC.[object_id]

                AND IDX.index_id=IDXC.index_id

        LEFT JOIN sys.key_constraints KC

            ON IDX.[object_id]=KC.[parent_object_id]

                AND IDX.index_id=KC.unique_index_id

        INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息

        (

            SELECT [object_id], Column_id, index_id=MIN(index_id)

            FROM sys.index_columns

            GROUP BY [object_id], Column_id

        ) IDXCUQ

            ON IDXC.[object_id]=IDXCUQ.[object_id]

                AND IDXC.Column_id=IDXCUQ.Column_id

                AND IDXC.index_id=IDXCUQ.index_id

    ) IDX

        ON C.[object_id]=IDX.[object_id]

            AND C.column_id=IDX.column_id 



-- WHERE O.name=N'要查询的表'       -- 如果只查询指定表,加上此条件

ORDER BY O.name,C.column_id 









-- 2. 索引及主键信息 



-- ========================================================================

-- 索引及主键信息



-- 邹建 2005.08(引用请保留此信息)

-- ========================================================================

SELECT 

    TableId=O.[object_id],

    TableName=O.Name,

    IndexId=ISNULL(KC.[object_id],IDX.index_id),

    IndexName=IDX.Name,

    IndexType=ISNULL(KC.type_desc,'Index'),

    Index_Column_id=IDXC.index_column_id,

    ColumnID=C.Column_id,

    ColumnName=C.Name,

    Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')

        WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,

    PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,

    [UQIQUE]=CASE WHEN IDX.is_unique=1 THEN N'√'ELSE N'' END,

    Ignore_dup_key=CASE WHEN IDX.ignore_dup_key=1 THEN N'√'ELSE N'' END,

    Disabled=CASE WHEN IDX.is_disabled=1 THEN N'√'ELSE N'' END,

    Fill_factor=IDX.fill_factor,

    Padded=CASE WHEN IDX.is_padded=1 THEN N'√'ELSE N'' END

FROM sys.indexes IDX

    INNER JOIN sys.index_columns IDXC

        ON IDX.[object_id]=IDXC.[object_id]

            AND IDX.index_id=IDXC.index_id

    LEFT JOIN sys.key_constraints KC

        ON IDX.[object_id]=KC.[parent_object_id]

            AND IDX.index_id=KC.unique_index_id

    INNER JOIN sys.objects O

        ON O.[object_id]=IDX.[object_id]

    INNER JOIN sys.columns C

        ON O.[object_id]=C.[object_id]

            AND O.type='U'

            AND O.is_ms_shipped=0

            AND IDXC.Column_id=C.Column_id

--    INNER JOIN  -- 对于一个列包含多个索引的情况,只显示第1个索引信息

--    (

--        SELECT [object_id], Column_id, index_id=MIN(index_id)

--        FROM sys.index_columns

--        GROUP BY [object_id], Column_id

--    ) IDXCUQ

--        ON IDXC.[object_id]=IDXCUQ.[object_id]

--            AND IDXC.Column_id=IDXCUQ.Column_id

dawugui 2009-01-22

打赏
举报

--sql 2000

1. SqlServer数据库字典--表结构.sql



SELECT TOP 100 PERCENT --a.id, 



CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名, 



CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明, 



a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, 



a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, 



CASE WHEN EXISTS 



(SELECT 1 



FROM dbo.sysindexes si INNER JOIN 



dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN 



dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN 



dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK' 



WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, 



b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') 



AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, 



CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '') 



AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间, 



CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间 



FROM dbo.syscolumns a LEFT OUTER JOIN 



dbo.systypes b ON a.xtype = b.xusertype INNER JOIN 



dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND 



d.status >= 0 LEFT OUTER JOIN 



dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN 



dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN 



dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 



ORDER BY d.name, a.colorder





----------------------------------------------------------------

2.SqlServer数据库字典--索引.sql



SELECT TOP 100 PERCENT --a.id, 



CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名, 



CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称, d.name AS 列名, 



b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending') 



WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL 



THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered') 



WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id, 



a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一, 



CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束, 



a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间 



FROM dbo.sysindexes a INNER JOIN 



dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN 



dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN 



dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN 



dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN 



dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK' 



WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id, 



N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0) 



ORDER BY c.name, a.name, b.keyno





-------------------------------------------------



3.SqlServer数据库字典--主键.外键.约束.视图.函数.存储过程.触发器.sql



SELECT DISTINCT 



TOP 100 PERCENT o.xtype, 



CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 'PK' THEN 



'主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' WHEN 'FN' 



THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' ELSE '存储过程' 



END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间, 



c.text AS 声明语句 



FROM dbo.sysobjects o LEFT OUTER JOIN 



dbo.syscomments c ON o.id = c.id 



WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND 



(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0) 



ORDER BY CASE o.xtype WHEN 'X' THEN '扩展存储过程' WHEN 'TR' THEN '触发器' WHEN 



'PK' THEN '主键' WHEN 'F' THEN '外键' WHEN 'C' THEN '约束' WHEN 'V' THEN '视图' 



WHEN 'FN' THEN '函数-标量' WHEN 'IF' THEN '函数-内嵌' WHEN 'TF' THEN '函数-表值' 



ELSE '存储过程' END DESC