怎么查找两个表中有相似的字段

[城池523]

例如：
A表字段名
aa abc bbb ccc
B表字段
afdd klsjf ksdj sdabc

我现在想要找出这两个表有相似名称的两个字段
即：A.abc = B.sdabc

[suinlove]

select object_name(a.id) 表A,a.name A_name,b.name B_name,object_name(b.id) 表B
from syscolumns a
join syscolumns b
on a.id=object_id('t1') --表A
and b.id=object_id('t2') --表B
and (a.name like '%'+b.name+'%'
or b.name like '%'+a.name+'%')

[花谢尊前不敢香]

charindex可行

[biany2]

引用 3 楼 chuanzhang5687 的回复:

select * from 
(
select name from sys.columns c,
sys.objects o where 
c.object_id = o.object_id
and o.name = 't1'
) t1,
(
select name from sys.columns c,
sys.objects o where 
c.object_id = o.object_id
and o.name = 't2'
) t2
where t1.name like t2.name

[lbb398229882]

用like就可以了

[szm341]

select * from a,b where A.abc = B.sdabc

[發糞塗牆]

用这个函数：charindex() 用法看联机丛书

[chuanzhang5687]

select * from 
(
select name from sys.columns c,
sys.objects o where 
c.object_id = o.object_id
and o.name = 't1'
) t1,
(
select name from sys.columns c,
sys.objects o where 
c.object_id = o.object_id
and o.name = 't2'
) t2
where t1.name like t2.name