select * from 表1 a,表2 b....表N n where a.编号=b.编号 and ..... and a.编号=n.编号
横向连接的话 select * from 表1 a,表2 b....表N n where a.编号=b.编号 and ..... and a.编号=n.编号 不过这样会很长,可能不便浏览 用union all来纵向连接比较方面看 感觉 利用系统表声称语句 select 'select * from '+t1.name+' where 编号= ''100000001''' from sysobjects t1,syscolumns t2 where t1.id=t2.id and t1.xtype='u' and t2.name='列名'
exec sp_msforeachtable N'if exists(select 1 from sysobjects a Inner join syscolumns b on a.id=b.id where a.id=object_id(''?'') and b.name=''编号'' and a.xtype=''U'') exec(''select * from ? '' +'' where 编号=''''100000001'''''') '
如果你的商品编号全库都是统一的,应该比较好找: --用游标 declare @name varchar(50) declare table #(id int identity,name varchar(20)) declare roy cursor for select object_name(id) from syscolumns where name='商品编号字段名' open roy fetch next from roy into @name while @@fetch_status=0 begin insert into # select @name exec('select * from '+@name +' where 商品编号字段名=100000001') fetch next from roy into @name end close roy deallocate roy--否则就用全文索引吧,比较麻烦
from 表1 a,表2 b....表N n
where a.编号=b.编号 and ..... and a.编号=n.编号
select *
from 表1 a,表2 b....表N n
where a.编号=b.编号 and ..... and a.编号=n.编号
不过这样会很长,可能不便浏览
用union all来纵向连接比较方面看 感觉
利用系统表声称语句
select 'select * from '+t1.name+' where 编号= ''100000001'''
from sysobjects t1,syscolumns t2
where t1.id=t2.id and t1.xtype='u' and t2.name='列名'
N'if exists(select 1 from sysobjects a Inner join syscolumns b on a.id=b.id where a.id=object_id(''?'') and b.name=''编号'' and a.xtype=''U'')
exec(''select * from ? '' +'' where 编号=''''100000001'''''')
'
--用游标
declare @name varchar(50)
declare table #(id int identity,name varchar(20))
declare roy cursor for select object_name(id) from syscolumns
where name='商品编号字段名'
open roy
fetch next from roy into @name
while @@fetch_status=0
begin
insert into # select @name
exec('select * from '+@name +' where 商品编号字段名=100000001')
fetch next from roy into @name
end
close roy
deallocate roy--否则就用全文索引吧,比较麻烦