Xrhrft

What would be the best way to implement this change?

I would work the other way around. Convert all your timestamps in the database to UTC, and just use UTC and go with the flow. If you need a timestamp in a different tz, you can create a generated column using AT TIME ZONE (as you did above) that renders the time stamp in that specified TZ (for the app). But, I would seriously consider just having UTC returned to the app, and writing that logic -- the display logic -- in the app.

Rather than export, manually edit, and rerun, you could try do the job directly in the database with something like:

DECLARE C CURSOR 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')

        ORDER BY so.name

DECLARE @SQL NVARCHAR(MAX), @ojtype NVARCHAR(MAX)

OPEN C

FETCH NEXT FROM C INTO @SQL, @ojtype

WHILE @@FETCH_STATUS = 0 BEGIN

    IF @objtype = 'P' SET @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE') 

    IF @objtype = 'V' SET @SQL = REPLACE(@SQL, 'CREATE VIEW'     , 'ALTER VIEW'     ) 

    SET @SQL = REPLACE(@SQL, 'GETDATE()', '[dbo].[getlocaldate]()') 

    --PRINT @SQL

    EXEC (@SQL)

    FETCH NEXT FROM C INTO @SQL, @ojtype

END

CLOSE C

DEALLOCATE C

of course extending it to deal with functions, triggers, and so forth too.

There are a few caveats:

• You may need to be a bit brighter and deal with different/extra white-space between CREATE and PROCEDURE/VIEW/<other>. Rather than the REPLACE for that you might prefer to instead leave the CREATE in place and execute a DROP first, but this risks leaving sys.depends and friends out of kilter where ALTER may not, also if ALTER fails you at least have the existing object still in place where with DROP+CREATE you may not.




• If you code has any "clever" smells like modifying its own schema with ad-hoc TSQL then you'll need to make sure the search and replace for CREATE->ALTER doesn't interfere with that.




• You will be wanting to regression test the entire application(s) after the operation, whether you use the cursor or export+edit+run methods.

I've used this method to make similar schema-wide updates in the past. It is a bit of a hack, and feels quite ugly, but sometimes it is the easiest/quickest way.

Defaults and other constraints can be modified similarly too, though those can only be dropped and recreated rather than altered. Something like:

DECLARE C CURSOR FOR

        SELECT AlterDefaultSQL = 'ALTER TABLE [' +st.name+ '] DROP CONSTRAINT [' + si.name + '];'

                               + CHAR(10)

                               + 'ALTER TABLE [' +st.name+ '] ADD CONSTRAINT [' + si.name + '] DEFAULT '+REPLACE(si.definition, 'GETDATE()', '[dbo].[getlocaldate]()')+' 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

DECLARE @SQL NVARCHAR(MAX)

OPEN C

FETCH NEXT FROM C INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

    --PRINT @SQL

    EXEC (@SQL)

    FETCH NEXT FROM C INTO @SQL

END

CLOSE C

DEALLOCATE C

Some more fun that you may need to contend with: if you are partitioning by time then those parts may need altering too. While partitioning on time more granularly then by day is rare you could have issues where DATETIMEs are interpreted by the partitioning function as being the previous or next day depending on timezine, leaving your partitions unaligned with your usual queries.

I really like David's answer and upvoted that for a programmatic way of doing things.

But you can try this today for a test-run in Azure via SSMS:

Right click your database --> Tasks --> Generate Scripts..

[Back Story] we had a junior DBA who upgraded all our test environments to SQL 2008 R2 while our production environments were at SQL 2008. It's a change that makes me cringe to this day. To migrate to production, from test, we had to generate scripts within SQL, using generate scripts, and in the advanced options we used the 'Type of Data to script: Schema and Data' option to generate a massive text file. We were successfully able to move our test R2 databases to our legacy SQL 2008 servers -- where a database restore to a lower version would not have worked. We used sqlcmd to input the large file--as the files were often too big for the SSMS text buffer.

What I'm saying here is that this option would probably work for you as well. You'll just need to do one additional step and search and replace getdate() with [dbo].getlocaldate in the generated text file. (I would put your function into the database before migration though).

(I never wanted to be proficient at this band-aid of a database restore, but for a while it became a defacto way of doing things. And, it worked every time.)

If you choose this route, be sure and select the Advanced button and select all the options you need (read each one) to move from the old database to the new database--like the defaults you mentioned. But give it a few test runs in Azure. I bet you'll find that this is one solution that works -- with a modicum of effort.

Dynamically alter all proc and udf to change value

    DECLARE @Text   NVARCHAR(max), 

        @spname NVARCHAR(max), 

        @Type   CHAR(5), 

        @Sql    NVARCHAR(max) 

DECLARE @getobject CURSOR 



SET @getobject = CURSOR 

FOR SELECT sc.text, 

           so.NAME, 

           so.type 

    FROM   sys.syscomments sc 

           INNER JOIN sysobjects so 

                   ON sc.id = so.id 

    WHERE  sc.[text] LIKE '%getdate()%' 



--and type in('P','FN') 

OPEN @getobject 



FETCH next FROM @getobject INTO @Text, @spname, @Type 



WHILE @@FETCH_STATUS = 0 

  BEGIN 

      IF ( @Type = 'P' 

            OR @Type = 'FN' ) 

        SET @Text = Replace(@Text, 'getdate', 'dbo.getlocaldate') 



      SET @Text = Replace(@Text, 'create', 'alter') 



      EXECUTE Sp_executesql 

        @Text 



      PRINT @Text 



      --,@spname,@Type 

      FETCH next FROM @getobject INTO @Text, @spname, @Type 

  END 



CLOSE @getobject 



DEALLOCATE @getobject  

    CREATE PROCEDURE [dbo].[Testproc1] 

AS 

    SET nocount ON; 



  BEGIN 

      DECLARE @CurDate DATETIME = Getdate() 

  END

Notice commented sysobjects Type column condition.My script will alter only proc and UDF.

This script will alter all Default Constraint which contain GetDate()

    DECLARE @TableName      VARCHAR(300), 

        @constraintName VARCHAR(300), 

        @colName        VARCHAR(300), 

        @Sql            NVARCHAR(max) 

DECLARE @getobject CURSOR 



SET @getobject = CURSOR 

FOR SELECT ds.NAME, 

           sc.NAME AS colName, 

           so.NAME AS Tablename 

    --,ds.definition 

    FROM   sys.default_constraints ds 

           INNER JOIN sys.columns sc 

                   ON ds.object_id = sc.default_object_id 

           INNER JOIN sys.objects so 

                   ON so.object_id = ds.parent_object_id 

    WHERE  definition LIKE '%getdate()%' 



OPEN @getobject 



FETCH next FROM @getobject INTO @constraintName, @colName, @TableName 



WHILE @@FETCH_STATUS = 0 

  BEGIN 

      SET @Sql = 'ALTER TABLE ' + @TableName 

                 + ' DROP CONSTRAINT ' + @constraintName + '; ' 

                 + Char(13) + Char(10) + '           ' + Char(13) + Char(10) + '' 

      SET @Sql = @Sql + ' ALTER TABLE ' + @TableName 

                 + ' ADD CONSTRAINT ' + @constraintName 

                 + '          DEFAULT dbo.GetLocaledate() FOR ' 

                 + @colName + ';' + Char(13) + Char(10) + '          ' + Char(13) 

                 + Char(10) + '' 



      PRINT @Sql 



      EXECUTE sys.Sp_executesql 

        @Sql 



      --,@spname,@Type 

      FETCH next FROM @getobject INTO @constraintName, @colName, @TableName 

  END 



CLOSE @getobject 



DEALLOCATE @getobject   

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 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