如何在不同的数据库中执行同一段代码

[liyaohui13098452233]

DECLARE cus_dbname CURSOR FOR SELECT  dbname from b_customer
OPEN cus_dbname    
declare @dbname varchar(10)
FETCH cus_dbname INTO @dbname
begin 
	use @dbname
	select 1
end 
FETCH cus_dbname INTO @dbname
CLOSE cus_dbname    
DEALLOCATE cus_dbname    

以上一是'@dbname' 附近有语法错误。

查询出服务器上有那些数据库，然后对应的数据库执行同一段代码，该如何写

[听雨停了]

引用 楼主 liyaohui13098452233 的回复:

DECLARE cus_dbname CURSOR FOR SELECT  dbname from b_customer
OPEN cus_dbname    
declare @dbname varchar(10)
FETCH cus_dbname INTO @dbname
begin 
	use @dbname
	select 1
end 
FETCH cus_dbname INTO @dbname
CLOSE cus_dbname    
DEALLOCATE cus_dbname    

以上一是'@dbname' 附近有语法错误。 查询出服务器上有那些数据库，然后对应的数据库执行同一段代码，该如何写

DECLARE cus_dbname CURSOR FOR SELECT  dbname from b_customer
OPEN cus_dbname    
declare @dbname varchar(10)
FETCH NEXT FROM cus_dbname INTO @dbname
WHILE @@FETCH_STATUS=0
begin 
	use @dbname
	select 1
	FETCH NEXT FROM cus_dbname INTO @dbname
end 
CLOSE cus_dbname    
DEALLOCATE cus_dbname    

语法有错误，游标应该这样写

[liyaohui13098452233]

引用 1 楼 qq_37170555 的回复:

[quote=引用 楼主 liyaohui13098452233 的回复:]

DECLARE cus_dbname CURSOR FOR SELECT  dbname from b_customer
OPEN cus_dbname    
declare @dbname varchar(10)
FETCH cus_dbname INTO @dbname
begin 
	use @dbname
	select 1
end 
FETCH cus_dbname INTO @dbname
CLOSE cus_dbname    
DEALLOCATE cus_dbname    

以上一是'@dbname' 附近有语法错误。 查询出服务器上有那些数据库，然后对应的数据库执行同一段代码，该如何写

DECLARE cus_dbname CURSOR FOR SELECT  dbname from b_customer
OPEN cus_dbname    
declare @dbname varchar(10)
FETCH NEXT FROM cus_dbname INTO @dbname
WHILE @@FETCH_STATUS=0
begin 
	use @dbname
	select 1
	FETCH NEXT FROM cus_dbname INTO @dbname
end 
CLOSE cus_dbname    
DEALLOCATE cus_dbname    

语法有错误，游标应该这样写[/quote] 还是提示'@dbname' 附近有语法错误。

[听雨停了]

引用 2 楼 liyaohui13098452233 的回复:

[quote=引用 1 楼 qq_37170555 的回复:] [quote=引用 楼主 liyaohui13098452233 的回复:]

DECLARE cus_dbname CURSOR FOR SELECT  dbname from b_customer
OPEN cus_dbname    
declare @dbname varchar(10)
FETCH cus_dbname INTO @dbname
begin 
	use @dbname
	select 1
end 
FETCH cus_dbname INTO @dbname
CLOSE cus_dbname    
DEALLOCATE cus_dbname    

以上一是'@dbname' 附近有语法错误。 查询出服务器上有那些数据库，然后对应的数据库执行同一段代码，该如何写

DECLARE cus_dbname CURSOR FOR SELECT  dbname from b_customer
OPEN cus_dbname    
declare @dbname varchar(10)
FETCH NEXT FROM cus_dbname INTO @dbname
WHILE @@FETCH_STATUS=0
begin 
	use @dbname
	select 1
	FETCH NEXT FROM cus_dbname INTO @dbname
end 
CLOSE cus_dbname    
DEALLOCATE cus_dbname    

语法有错误，游标应该这样写[/quote] 还是提示'@dbname' 附近有语法错误。[/quote]

DECLARE cus_dbname CURSOR FOR SELECT  dbname from b_customer
OPEN cus_dbname    
declare @dbname varchar(10),@sql VARCHAR(max)
FETCH NEXT FROM cus_dbname INTO @dbname
WHILE @@FETCH_STATUS=0
begin 
	SET @sql = 'use ' + @dbname + ';
	go;
	select 1'
	exce (@sql)
	FETCH NEXT FROM cus_dbname INTO @dbname
end 
CLOSE cus_dbname    
DEALLOCATE cus_dbname    

只能写成动态sql来执行，直接这样use @dbname不行的

[liyaohui13098452233]

又提示这个'go' 附近有语法错误。

[听雨停了]

引用 4 楼 liyaohui13098452233 的回复:

又提示这个'go' 附近有语法错误。

把go;去掉

SET @sql = 'use ' + @dbname + '; '+
   'select 1'

[吉普赛的歌]

--举例：显示所有非系统库的每一个用户库中的表的个数
EXEC sp_MSforeachdb 'if "?" not in ("tempdb","master","model","msdb") select count(1) as cnt from ?.sys.tables'

[吉普赛的歌]

--举例：显示所有非系统库中的表的个数
EXEC sp_MSforeachdb 'if "?" not in ("tempdb","master","model","msdb") select "?" as dbName, count(1) as cnt from ?.sys.tables'

[日月路明]

只能用动态sql完成