--
----------创建临时表---------------------
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'A')
DROP TABLE A
CREATE TABLE [dbo].[A](
[TableName] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL,
[ColumnName] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NULL
)
DELETE FROM A
----------
----------遍历需要更改的字段---------------------
DECLARE @ColumnName nvarchar(100)
DECLARE @strSQL nvarchar(4000)
DECLARE myCursor CURSOR
FOR SELECT M.NAME,S.NAME AS ColumnName
FROM sysobjects M
INNER JOIN syscolumns S ON M.ID = S.ID
WHERE M.xtype = 'U' AND LEFT(M.NAME,2) = 'ND'
AND S.xtype IN(167, 231)
ORDER BY M.NAME
OPEN myCursor
FETCH NEXT FROM myCursor INTO @TableName,@ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
--查询字段
SET @strSQL = 'SELECT * FROM ' + @TableName + ' WHERE '+ @ColumnName +' LIKE ''%TBL_%'''
SET @strSQL = 'IF EXISTS(' + @strSQL + ')' + CHAR(13) + CHAR(10)
SET @strSQL = @strSQL + 'INSERT INTO A VALUES('''+@TableName+''','''+@ColumnName+''') '
SET @strSQL = @strSQL + CHAR(13) + CHAR(10)
SET @strSQL = @strSQL + CHAR(13) + CHAR(10)
EXEC SP_EXECUTESQL @strSQL
--print @strSQL
FETCH NEXT FROM myCursor INTO @TableName,@ColumnName
END
CLOSE myCursor
DEALLOCATE myCursor
-----------------------生成更新语句---------------------------
DECLARE myCursor CURSOR
FOR SELECT TableName,ColumnName FROM A ORDER BY TABLENAME
OPEN myCursor
FETCH NEXT FROM myCursor INTO @TableName,@ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @strSQL = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + ' = REPLACE('+@ColumnName+',''TBL_'',''UT_'') '
EXEC SP_EXECUTESQL @strSQL
--PRINT @TableName + '~~~~~~' + @ColumnName
--PRINT @strSQL
FETCH NEXT FROM myCursor INTO @TableName,@ColumnName
END
CLOSE myCursor
DEALLOCATE myCursor
----------------------更新视图-----------------
---删除临时表
-- IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'A')
-- DROP TABLE A
