Changing the use of GETDATE() in the entire database

1. Use the SQL Server tool to export the database objects definition to a SQL file which should include: tables, views, triggers, SPs, functions, and so on

2. Edit the SQL file (make a backup first) using any text editor that allows you to find the text "GETDATE()" and replace it with "[dbo].[getlocaldate]()"

3. Run the edited SQL file in Azure SQL to create your database objects...

4. Execute the migration of data.

Here you have a reference from azure documentation: Generating Scripts for SQL Azure

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.