Create PROC [dbo].[GetAllThing]( @oldString NVARCHAR(100),@newString NVARCHAR(100) ) AS BEGIN DECLARE @tablename VARCHAR(100) DECLARE @columnname VARCHAR(100) DECLARE @columnCount INT DECLARE @tmpTable VARCHAR(100) SET @tmpTable = '' --查询的到库中的所有用户表 --SELECT name FROM sysobjects WHERE xtype='U' BEGIN TRAN DECLARE tmpTableName CURSOR FOR SELECT name FROM sysobjects WHERE xtype='U' OPEN tmpTableName FETCH NEXT FROM tmpTableName INTO @tablename WHILE @@FETCH_STATUS=0 BEGIN SET @columnname='' --查询的到TemplateInfo表中的所有字段 --SELECT NAME FROM syscolumns s WHERE OBJECT_NAME(id)='TemplateInfo' BEGIN TRan DECLARE tmpColumnName CURSOR FOR SELECT NAME FROM syscolumns s WHERE OBJECT_NAME(ID)=@tablename select @columnCount= COUNT(NAME) FROM syscolumns WHERE OBJECT_NAME(id)=@tablename OPEN tmpColumnName FETCH NEXT FROM tmpColumnName INTO @columnname WHILE @@FETCH_STATUS=0 BEGIN set @tmpTable ='UPDATE '+ @tablename +'set '+@columnname + ' = ' +@newString +' WHERE '+@columnname+' LIKE ''%' +LOWER(@oldString)+'%' EXEC (@tmpTable) FETCH NEXT FROM tmpColumnName INTO @columnname END COMMIT TRAN CLOSE tmpColumnName DEALLOCATE temColumnName FETCH NEXT FROM tmpTableName INTO @tablename END COMMIT TRAN CLOSE tmpTableName DEALLOCATE tmpTableName END