我要从sysobjects里面查找所有的以Y开头的表名(用户表),然后在这些表里面查找数据,要怎么写过程?需要的是类似下面的结果(这个肯定是不行的):
select * from (select name from sysobjects where type='U' and name like 'Y%') where barcode='1234'
select * from (select name from sysobjects where type='U' and name like 'Y%') where barcode='1234'
declare @tb nvarchar(50),@sql nvarchar(2000)
select @tb=name from sysobjects where type='U' and name like 'Y%'
set @sql='select * from '+@tb+' where barcode=''1234'' '
exec(@sql)如果你有一个以上y开头的表,需要写游标一个表一个表的查询
不明白你这样写有什么意思,如果返回的是两个表呢。declare @sql varchar(max)
set @sql = ''
select @sql = @sql+ ' select * from '+ t.name +'where barcode=''1234'' '
from sysobjects as t join sys.all_columns as c on t.id = c.object_id and t.type='U' and c.name ='barcode' and t.name like 'Y%'
exec (@sql)
--sql 2008有自己的目录视图
use dbname
go
select * from sys.tables
where name like 'y%'
drop proc Proc_Search_XML;
GOcreate proc Proc_Search_XML
@ModelName nvarchar(30)='%',
@BarcodeNo nvarchar(30)='%',
@Station nvarchar(5)='%',
@Search_1 nvarchar(10)='%',
@Search_2 nvarchar(10)='%'
AS
set nocount on;
BEGIN
--声明编号
declare @tb_name nvarchar(10)
--声名游标
declare tb_name_cursor cursor local for
select name from sysobjects where type='U' and name like 'Y%'
order by crdate desc--声名SQL字符串
declare @sqlCMD nvarchar(1000)--打开游标
open tb_name_cursor--开始遍历
set @sqlCMD=''
fetch next from tb_name_cursor into @tb_name
while @@fetch_status=0
begin
--生成查询字符串
if (@sqlCMD<>'')
begin
set @sqlCMD = @sqlCMD+' union '
end set @sqlCMD=@sqlCMD+'select * from '+@tb_name+' where 1=1 '
--查询条件:ModelName
if(@ModelName <> '%' and @ModelName <> '')
begin
set @sqlCMD=@sqlCMD+' and ModelName='+@ModelName
end --查询条件:BarcodeNo
if(@BarcodeNo <> '%' and @BarcodeNo <> '')
begin
set @sqlCMD=@sqlCMD+' and BarcodeNo='+''+@BarcodeNo+''
end
--查询条件:Station
if(@Station <> '%' and @Station <> '')
begin
set @sqlCMD=@sqlCMD+' and Station='+@Station
end --查询条件:Start Time
if(@Search_1 <> '%' and @Search_1 <> '')
begin
set @sqlCMD=@sqlCMD+' and Upload_Date >='+@Search_1
end --查询条件:Last Time
if(@Search_2 < '%' and @Search_2 <> '')
begin
set @sqlCMD=@sqlCMD+' and Upload_Date <='+@Search_2
end--下一个
fetch next from tb_name_cursor into @tb_name
end
--关闭游标
close tb_name_cursor
--释放游标
deallocate tb_name_cursor--运行查询命令
print @sqlCMD
exec @sqlCMD
END
GO
运行:exec proc_search_xml '','1234','','',''
提示:
select * from Y2013_2 where 1=1 and BarcodeNo=1234 union select * from Y2013_1 where 1=1 and BarcodeNo=1234
訊息 2812,層級 16,狀態 62,行 77
Could not find stored procedure 'select * from Y2013_2 where 1=1 and BarcodeNo=1234 union select * from Y2013_1 where 1=1 and BarcodeNo=1234'.