[求助]如何快速查找表中的字段

[gong_max]

数据的很多表中都拥有同一个字段（名字相同，但表之间不存在外键关联），比如creditCard，我现在想找出哪些表中有这个字段，请教大家，我该如何查找。

[-晴天]

select b.name from sysobjects b inner join syscolumns b on a.id=b.id and a.name='creditCard'

[-晴天]

select b.name from sysobjects b inner join syscolumns a on a.id=b.id and a.name='creditCard'

[qin_0]

你给每个字段起一个别名嘛

[--小F--]

EXEC sp_msforeachtable 
        @command1 = N'declare @l varchar(1000); exec getFieldAll ''?'',''aa_1'',@l out;if @l<>'''' select ''?'' 表名,@l 列名

[--小F--]

select a.name as TableName,b.name as ColName From sysobjects a
inner join syscolumns b on (a.id=b.id)
where a.xtype='U' and b.name=字段名

[--小F--]

[Quote=引用 2 楼 qianjin036a 的回复:]
SQL code

select b.name from sysobjects b inner join syscolumns a on a.id=b.id and a.name='creditCard'
[/Quote]
最好加上 a.xtype='U'

[jyh070207]

select s.name as table_name,b.name as column_name from sysobjects s left join syscolumns b on s.id = b.id
where s.xtype = 'u'
and b.name = 'creditCard

[wynlc]

select s.name as table_name,b.name as column_name from sysobjects s left join syscolumns b on s.id = b.id
where
s.xtype = 'u'   --用户表
and 
b.name = 'id'

结果：
table_name                                                                                                                       column_name
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
t1                                                                                                                               id
t2                                                                                                                               id

(2 行受影响)

[geniuswjt]

借助系统表sys.tables和sys.columns

[gong_max]

[Quote=引用 5 楼 fredrickhu 的回复:]

SQL code
select a.name as TableName,b.name as ColName From sysobjects a
inner join syscolumns b on (a.id=b.id)
where a.xtype='U' and b.name=字段名
[/Quote]
谢谢，问题解决！