--在其它数据库执行以一代码,如数据库 DBA ,使用游标打开数据库[200906]下的[sysobjects] 返回_detail结尾的表名
--问题 exec('use ['+@Dbname+']') ,动态执行打开数据库,执行完成后,
--游标仍然查找的是数据库DBA里以_detail结尾的表名
--如何动态执行 use DBName,
--或者游标直接运行SELECT top 2 name FROM [200906].[dbo].[sysobjects] where type='U' and name like '%_detail'
declare @TableName varchar(20); --定义表名
declare @SubTabName varchar(20); --表名截取
declare @DbName varchar(20);
declare @DateFlag varchar(30);
declare @SubT1 varchar(1) set @DateFlag=convert(varchar,dateadd(mm,-1,getdate()),112);
set @DbName=substring(@DateFlag,1,6)
select @DateFlag,@DbName
exec('use ['+@Dbname+']') --打开数据库
--游标打sysobjects,目 标是数据库 @Dbname 下的[sysobjects] declare Cur_TableName CURSOR FOR SELECT top 2 name FROM [dbo].[sysobjects] where type='U' and name like '%_detail'; OPEN Cur_TableName; FETCH NEXT FROM Cur_TableName INTO @TableName; WHILE @@fetch_status = 0
begin
set @TableName=ltrim(rtrim(@TableName)) --去除表名空格
set @SubT1=substring(@TableName,1,1);
if(@SubT1='8')
Begin
set @SubTabName=substring(@TableName,1,5); --截取号段
End
else
Begin
set @SubTabName=substring(@TableName,1,8); --截取号段
End
select @TableName,@SubTabName
FETCH NEXT FROM Cur_TableName INTO @TableName ;
end
close Cur_TableName
DEALLOCATE Cur_TableName
--问题 exec('use ['+@Dbname+']') ,动态执行打开数据库,执行完成后,
--游标仍然查找的是数据库DBA里以_detail结尾的表名
--如何动态执行 use DBName,
--或者游标直接运行SELECT top 2 name FROM [200906].[dbo].[sysobjects] where type='U' and name like '%_detail'
declare @TableName varchar(20); --定义表名
declare @SubTabName varchar(20); --表名截取
declare @DbName varchar(20);
declare @DateFlag varchar(30);
declare @SubT1 varchar(1) set @DateFlag=convert(varchar,dateadd(mm,-1,getdate()),112);
set @DbName=substring(@DateFlag,1,6)
select @DateFlag,@DbName
exec('use ['+@Dbname+']') --打开数据库
--游标打sysobjects,目 标是数据库 @Dbname 下的[sysobjects] declare Cur_TableName CURSOR FOR SELECT top 2 name FROM [dbo].[sysobjects] where type='U' and name like '%_detail'; OPEN Cur_TableName; FETCH NEXT FROM Cur_TableName INTO @TableName; WHILE @@fetch_status = 0
begin
set @TableName=ltrim(rtrim(@TableName)) --去除表名空格
set @SubT1=substring(@TableName,1,1);
if(@SubT1='8')
Begin
set @SubTabName=substring(@TableName,1,5); --截取号段
End
else
Begin
set @SubTabName=substring(@TableName,1,8); --截取号段
End
select @TableName,@SubTabName
FETCH NEXT FROM Cur_TableName INTO @TableName ;
end
close Cur_TableName
DEALLOCATE Cur_TableName
for select name from sys.databases
declare @SQL varchar(max),@DName varchar(100),@Error int
open r_d
fetch next from r_d into @DName
while @@fetch_status=0
begin
set @sql='use '+@DName+';'
set @sql=@sql+'
declare R_T cursor
for select name from sys.tables
declare @T varchar(100)
open r_t
fetch next from r_t into @t
while @@fetch_status=0
begin
select * from sys.tables
fetch next from r_index into @TName,@Iname,@avg
end
close r_index
deallocate r_index
fetch next from r_t into @t
end
close r_t
deallocate r_t'
fetch next from r_d into @DName
end
close r_d
deallocate r_d
declare @DateFlag varchar(30); set @DateFlag=convert(varchar,dateadd(mm,-1,getdate()),112);
set @DbName=substring(@DateFlag,1,6)
select @DateFlag,@DbName
exec('use ['+@Dbname+'] declare @TableName varchar(20); --定义表名
declare @SubTabName varchar(20); --表名截取
declare @SubT1 varchar(1)
--游标打sysobjects,目 标是数据库 @Dbname 下的[sysobjects] declare Cur_TableName CURSOR FOR SELECT top 2 name FROM [dbo].[sysobjects] where type=''U'' and name like ''%_detail''; OPEN Cur_TableName; FETCH NEXT FROM Cur_TableName INTO @TableName; WHILE @@fetch_status = 0
begin
set @TableName=ltrim(rtrim(@TableName)) --去除表名空格
set @SubT1=substring(@TableName,1,1);
if(@SubT1=''8'')
Begin
set @SubTabName=substring(@TableName,1,5); --截取号段
End
else
Begin
set @SubTabName=substring(@TableName,1,8); --截取号段
End
select @TableName,@SubTabName
FETCH NEXT FROM Cur_TableName INTO @TableName ;
end
close Cur_TableName
DEALLOCATE Cur_TableName
')
验证了下,2楼的方法可行。
其实你动态语句也可以这样用,比如:
declare @STR varchar(50)
declare @DBNAME varchar(50)
declare @TBNAME varchar(50) set @DBNAME='DB'
set @TBNAME='TB'
set @STR='select top 100 * from '+@DBNAME+'.dbo.'+@TBNAME
print @STR
exec(@STR)
The body of the stored procedure contains the batch of commands you want to execute
within the procedure. The following are the only commands that you cannot execute
within a stored procedure:
■ SET SHOWPLAN_TEXT
■ SET SHOWPLAN_ALL
■ USE <database>