导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

如何查询出当前数据表主健有无被其他数据作为外健

mydreamf 2007-12-21 05:55:31
如题!!高手帮忙,小弟实在没则了!!!
...全文
54 点赞 收藏 7
写回复
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
mydreamf 2008-04-01
汗,既然忘了结贴了
对不住大家拉
回复
-狙击手- 2007-12-21
create table ta(id int PRIMARY KEY)

go
create table tb(id int primary key ,col int REFERENCES ta(id))
go

---1

sp_fkeys 'ta'
----2
select * from sysforeignkeys
/*
PKTABLE_QUALIFIER PKTABLE_OWNER PKTABLE_NAME PKCOLUMN_NAME FKTABLE_QUALIFIER FKTABLE_OWNER FKTABLE_NAME FKCOLUMN_NAME KEY_SEQ UPDATE_RULE DELETE_RULE FK_NAME PK_NAME DEFERRABILITY
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------- ----------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------
CSDN_TEST dbo ta id CSDN_TEST dbo tb col 1 1 1 FK__tb__col__417BF6D5 PK__ta__3E9F8A2A 7

constid fkeyid rkeyid fkey rkey keyno
----------- ----------- ----------- ------ ------ ------
1098643157 1066643043 1034642929 2 1 1

(所影响的行数为 1 行)

*/

drop table tb,ta
回复
Limpire 2007-12-21
if exists (select 1 from sysforeignkeys where rkeyid = object_id('要查的表'))
print '有外键'
else
print '无外键'
回复
Limpire 2007-12-21
if exists (select 1 from sysforeignkeys where object_name(rkeyid) = '要查的表')
print '有外键'
else
print '无外键'
回复
中国风 2007-12-21
select [表名]=object_name(rkeyid),[列名]=b.Name,
[引用表]= object_name(fkeyID),[引用列]=c.Name
from
sysforeignkeys a
join
syscolumns b on a.rkeyid=b.ID and b.colid=a.rkey
join
syscolumns c on c.ID=a.fkeyID and c.colid=a.fkey
where rkeyid=object_id('表名')
回复
中国风 2007-12-21
sysforeignkeys
\sysreferences
--以上两个表都可以实现
回复
dawugui 2007-12-21
--获取MS SQL库数据字典的经典SQL语句,然后从这里找.

SELECT sysobjects.name AS [table], sysproperties.[value] AS 表说明,
syscolumns.name AS field, properties.[value] AS 字段说明, systypes.name AS type,
syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,
'Scale'), 0) AS 小数位数, syscolumns.isnullable AS isnull,
CASE WHEN syscomments.text IS NULL
THEN '' ELSE syscomments.text END AS [Default],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = 'PK' AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN '√' ELSE '' END AS 主键
FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id
WHERE (sysobjects.xtype = 'U')
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告