查找某个字段在数据库中的引用

[jasonrush]

我用的是SQL2008,
有个一个部门表T_Deptment,
有两个字段: Deptment_ID int , Deptment varchar(20)
我怎样才能查找到,所有引用Deptment_ID的表啊?
或者将所有包含Deptment_ID的表列出来啊?

[bancxc]

引用 外键吗

[budong0000]

sp_fkey 'T_Deptment'

[bancxc]

select name from sysobjects where type='u'
and id in (select id from syscolumns where name='Deptment_ID')

[无锡阔微信息科技]

写个存储过程循环表，查看该表的约束信息

[budong0000]

sp_fkeys

[永生天地]

[Quote=引用楼主 jasonrush 的回复:]
我用的是SQL2008,
有个一个部门表T_Deptment,
有两个字段: Deptment_ID int , Deptment varchar(20)
我怎样才能查找到,所有引用Deptment_ID的表啊?
或者将所有包含Deptment_ID的表列出来啊?
[/Quote]

没有2008,给顶

[快乐_石头]

select table_name,
       column_name
from information_schema.columns
where column_name='Deptment_ID'

[--小F--]

select
 name
from
 sysobjects 
where
 type='u'
and
 id in (select id from syscolumns where name='Deptment_ID')

[永生天地]

[Quote=引用 2 楼 budong0000 的回复:]
SQL code
sp_fkey 'T_Deptment'
[/Quote]

这个好像有

[一直活在明天]

查一个数据所有表
select name from sysobjects where xtype = 'u'
查所有包含某一列名的所有表。
select obj.name from sysobjects obj,syscolumns col where col.id = obj.id and col.name='userid'

[vipper23]

sp_depends T_Deptment

[xman_78tom]

select OBJECT_NAME(parent_object_id)
from sys.foreign_key_columns
where referenced_object_id=OBJECT_ID('T_Deptment') and
referenced_column_id=(select column_id from sys.columns 
where [object_id]=OBJECT_ID('T_Deptment') and name='Deptment_ID');

[xman_78tom]

-- 查询引用 @tabname 表上 @colname 列的表和列
declare @tabname sysname, @colname sysname;

select OBJECT_NAME(parent_object_id) table_name,
COL_NAME(OBJECT_ID(@tabname),parent_column_id) column_name
from sys.foreign_key_columns
where referenced_object_id=OBJECT_ID(@tabname) and
referenced_column_id=COLUMNPROPERTY(OBJECT_ID(@tabname),@colname,'columnid');