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

[mydreamf]

如题！！高手帮忙，小弟实在没则了！！！

[dawugui]

--获取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') 

[中国风]

sysforeignkeys
\sysreferences
--以上两个表都可以实现

[中国风]

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('表名')

[Limpire]

if exists (select 1 from sysforeignkeys where object_name(rkeyid) = '要查的表')
	print '有外键'
else
	print '无外键'

[Limpire]

if exists (select 1 from sysforeignkeys where rkeyid = object_id('要查的表'))
    print '有外键'
else
    print '无外键'

[-狙击手-]

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

[mydreamf]

汗，既然忘了结贴了
对不住大家拉