I have upvoted Evan Carrolls answer, as I think this is the best solution. I have not been able to convince my colleagues that they should change a lot of C# code, so I had to use the code that David Spillett wrote. I have fixed a couple of problems with UDFs, Dynamic SQL and Schemas (not all code use "dbo.") like this:
DECLARE C CURSOR LOCAL STATIC FOR
SELECT sm.definition, so.type
FROM sys.objects so
JOIN sys.all_sql_modules sm ON sm.object_id = so.object_id
WHERE so.type IN ('P', 'V')
AND CHARINDEX('getdate()', sm.definition) > 0
ORDER BY so.name
DECLARE @SQL NVARCHAR(MAX), @objtype NVARCHAR(MAX)
OPEN C
WHILE 1=1 BEGIN
FETCH NEXT FROM C INTO @SQL, @objtype
IF @@FETCH_STATUS <> 0 BREAK
IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE') /* when you write "create or alter proc" */
IF @objtype = 'V' SET @SQL = REPLACE(@SQL, 'CREATE VIEW' , 'ALTER VIEW' )
IF CHARINDEX('getdate())''', @sql) > 0 BEGIN /* when dynamic SQL is used */
IF CHARINDEX('utl.getdate())''', @sql) = 0 SET @SQL = REPLACE(@SQL, 'GETDATE()', 'utl.getdate()')
end
ELSE begin
SET @SQL = REPLACE(@SQL, 'GETDATE()', 'CONVERT(DATETIME, CONVERT(datetimeoffset, SYSDATETIME()) AT TIME ZONE ''Central Europe Standard Time'')')
end
EXEC dbo.LongPrint @String = @sql
EXEC (@SQL)
END
CLOSE C
DEALLOCATE C
and the default constraints like this:
DECLARE C CURSOR LOCAL STATIC FOR
SELECT AlterDefaultSQL = 'ALTER TABLE [' +sch.name+ '].[' +st.name+ '] DROP CONSTRAINT [' + si.name + '];'
+ CHAR(10)
+ 'ALTER TABLE [' +sch.name+ '].[' +st.name+ '] ADD CONSTRAINT [' + si.name + '] DEFAULT '+REPLACE(si.definition, 'GETDATE()', 'CONVERT(DATETIME, CONVERT(datetimeoffset, SYSDATETIME()) AT TIME ZONE ''Central Europe Standard Time'')')+' FOR '+sc.name+';'
FROM sys.tables st
JOIN sys.default_constraints si ON si.parent_object_id = st.object_id
JOIN sys.columns sc ON sc.default_object_id = si.object_id
INNER JOIN sys.schemas sch ON sch.schema_id = st.schema_id
WHERE CHARINDEX('getdate()', si.definition) > 0
ORDER BY st.name, sc.name
DECLARE @SQL NVARCHAR(MAX)
OPEN C
WHILE 1=1 BEGIN
FETCH NEXT FROM C INTO @SQL
IF @@FETCH_STATUS <> 0 BREAK
EXEC dbo.LongPrint @String = @sql
EXEC (@SQL)
FETCH NEXT FROM C INTO @SQL
END
CLOSE C
DEALLOCATE C
UDFs
The suggestion to use a UDF that returns todays date and time looks nice, but I think there are still enough performance problems with UDFs, so I've chosen to use the very long and ugly AT TIME ZONE solution.