更新所有数据库

DECLARE @dbname VARCHAR(50)
DECLARE @t table(id INT IDENTITY(1,1),dbname VARCHAR(50))
DECLARE @i int,@count INT
DECLARE @sql nVARCHAR(MAX)
select @i = 1,@count = 0
INSERT INTO @t(dbname) SELECT NAME FROM MASTER.DBO.SYSDATABASES where name not in (‘master’,’model’,’msdb’,’tempdb’,’ReportServer’,’ReportServerTempDB’) ORDER BY NAME
SELECT @count = COUNT(1) FROM @t
WHILE @i <= @count
BEGIN
SELECT @dbname = dbname FROM @t WHERE id = @i
PRINT @dbname
SET @sql = ‘use [‘+@dbname +’]’+ char(13)+char(10)+
‘
update maintainitem set 维修项目代码= isnull(维修项目代码,’’’’) where 1=0
‘
begin try
print @sql
EXEC (@sql)
select @dbname as db,’成功’ AS ErrMsg
end try
begin catch
—SELECT ERROR_NUMBER() AS ErrNum,ERROR_SEVERITY()AS ErrSev,ERROR_STATE() AS ErrState,ERROR_PROCEDURE() AS ErrProc,ERROR_LINE()AS ErrLine,ERROR_MESSAGE()AS ErrMsg
select @dbname as db, ERROR_MESSAGE()AS ErrMsg
end catch
SET @i = @i+1
END

—@sqlOperateText，
—输入纯sql语句，把单引号替换为2个单引号，替换1次即可,但CATCH不能截获【表名不存在等错误】,
—如果要截获就需要在catch里用exec执行，即就需要用@sqlProcText,把语句中的单引号替换为2个单引号，替换2次,即1个单号变成4个
—例如:
— 使用@sqlOperateText 语句为：set @sqlOperateText = ‘update users set username=’’xxx’’ where 1=0 ‘
— 使用@sqlProcText 语句为：set @sqlProcText = ‘update users set username=’’’’xxx’’’’ where 1=0 ‘
DECLARE @sqlOperateText VARCHAR(MAX)
—@sqlProcText主要更新存储过程/函数内容，需要把单引号替换为2个单引号，替换2次,即1个单号变成4个
—例如:
— set @sqlProcText =
/ ‘
alter proc pp_test as
BEGIN
select ‘’’’xxx’’’’ as xxx
END
‘
/
DECLARE @sqlProcText VARCHAR(MAX)
SET @sqlOperateText =
‘
—update users set username=’’xxx’’ where 1=0
‘
—即1个单号变成4个
—即1个单号变成4个
—即1个单号变成4个
—即1个单号变成4个
—即1个单号变成4个
—即1个单号变成4个
—不要带go
—仅一个存储过程
SET @sqlProcText =
‘
create proc pp_test
as
BEGIN
select ‘’’’bbb’’’’ as xxx
END
‘
DECLARE @dbname VARCHAR(50)
DECLARE @t table(id INT IDENTITY(1,1),dbname VARCHAR(50))
DECLARE @i int,@count INT
DECLARE @sql VARCHAR(MAX)
select @i = 1,@count = 0
INSERT INTO @t(dbname) SELECT NAME FROM MASTER.DBO.SYSDATABASES where NAME NOT IN (‘master’,’model’,’msdb’,’tempdb’) ORDER BY NAME
SELECT @count = COUNT(1) FROM @t
WHILE @i <= @count
BEGIN
SELECT @dbname = dbname FROM @t WHERE id = @i
PRINT ‘开始执行：’+@dbname
—执行单独语句
IF(isnull(ltrim(rtrim(@sqlOperateText)),’’)!=’’)
begin
SET @sql =
‘ BEGIN TRY use ‘+@dbname +CHAR(13)+
@sqlOperateText +
‘ END TRY
BEGIN CATCH
SELECT db_name() as dbname,ERROR_MESSAGE() AS ErrorMessage ,
ERROR_SEVERITY() AS ErrorSeverity ,
ERROR_STATE() AS ErrorState
END CATCH ‘
—PRINT @sql
EXEC (@sql)
end
IF(isnull(ltrim(rtrim(@sqlProcText)),’’)!=’’)
BEGIN
—执行更新存储过程内容
SET @sql =
‘ BEGIN TRY use ‘+@dbname +CHAR(13)+
‘ declare @innerSql varchar(max)
set @innerSql = ‘’ ‘+@sqlProcText+’ ‘’
exec(@innerSql)
‘+
‘ END TRY
BEGIN CATCH
SELECT db_name() as dbname,ERROR_MESSAGE() AS ErrorMessage ,
ERROR_SEVERITY() AS ErrorSeverity ,
ERROR_STATE() AS ErrorState
END CATCH ‘
—PRINT @sql
EXEC (@sql)
end
SET @i = @i+1
END