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

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

if exists (select 1 from sysforeignkeys where rkeyid = object_id('要查的表'))

    print '有外键'

else

    print '无外键'

if exists (select 1 from sysforeignkeys where object_name(rkeyid) = '要查的表')

	print '有外键'

else

	print '无外键'

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

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