T-SQL批量修改所有表中datetime类型的字段

由来

客户使用的系统是一个使用SQL Server 2000作为后台数据库的进销存系统，在1月1日的时候，由于客户误操作，将数据库服务器的系统时间调整至1月29日，与当天下午又调整至正确时间，所以导致账目不对。

公司要我替客户解决此问题，于是编写了如下TSQL脚本，贴在此处作为笔记，以备查询。

代码

--声明变量
DECLARE @table_name nvarchar(80),@column_name nvarchar(80),@SQL nvarchar(400)

--定义游标
DECLARE db_cursor CURSOR FOR 
SELECT a.name as '表',b.name as '列'
FROM sysobjects a,syscolumns b,systypes c
WHERE (a.id = b.id) AND (a.xtype = 'U' AND a.type = 'U') AND
 (((b.type = c.type or b.xtype = c.xtype) AND c.name = 'datetime') AND (b.name = 'oper_date'))

--打开游标
OPEN db_cursor

--读取游标
FETCH NEXT FROM db_cursor
INTO @table_name,@column_name

--判断游标状态
WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT '修改表：' + @table_name
	--29日往前28天既是1日
	SET @SQL = 'UPDATE ' + @table_name + ' set ' + @column_name + ' = DATEADD(dd,-28,' + @column_name + ') WHERE (YEAR(' + @column_name + ') = 2013) AND (MONTH(' + @column_name + ') = 1) AND (DAY(' + @column_name + ') = 29)'

	PRINT '生成的SQL: ' + @SQL
	PRINT '执行语句'

	EXEC(@SQL)	

	--读取游标下一条记录
	FETCH NEXT FROM db_cursor
	INTO @table_name,@column_name
END

PRINT 'TSQL执行完成'

--关闭游标
CLOSE db_cursor
--释放游标
DEALLOCATE db_cursor

说明

主要思路是检索系统表，找出数据库中字段是datetime类型的所有表，然后生成相应的UPDATE语句，修改表中的记录。

在数据库的系统表sysobjects中记录了数据库的所有对象，表字段xtype或者type为'U'则表明该对象是用户表。系统表syscolumns记录了数据库所有表的字段信息，包括字段名及表ID等，表字段xtype或者type是数据类型标示ID，系统数据类型记录在表systypes中，详细说明可以参考联机丛书，要善于使用帮助。

以上代码稍作修改就可以应用于其他特定类型的字段或者指定字段的值。