本文介绍了一种SQL查询方法，用于获取表中关于外键的详细信息，包括外键和主键的表名、列名及外键的级联更新和删除属性。此外还提供了一个查询表修改时间的方法。
- 获取表修改时间：SELECT modify_date FROM SYS.OBJECTS WHERE object_id=OBJECT_ID('table_Name')
- SELECT
外键表ID
=
b.fkeyid ,
外键表名称
=
object_name
(b.fkeyid) ,
外键列ID
=
b.fkey ,
外键列名
=
(
SELECT
name
FROM
syscolumns
WHERE
colid
=
b.fkey
AND
id
=
b.fkeyid) ,
主键表ID
=
b.rkeyid ,
主键表名
=
object_name
(b.rkeyid) ,
主键列ID
=
b.rkey ,
主键列名
=
(
SELECT
name
FROM
syscolumns
WHERE
colid
=
b.rkey
AND
id
=
b.rkeyid) ,
级联更新
=
ObjectProperty
(a.id,
'
CnstIsUpdateCascade
'
) ,
级联删除
=
ObjectProperty
(a.id,
'
CnstIsDeleteCascade
'
)
FROM
sysobjects a
join
sysforeignkeys b
on
a.id
=
b.constid
join
sysobjects c
on
a.parent_obj
=
c.id
where
a.xtype
=
'
f
'
AND
c.xtype
=
'
U
'
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
