Alterando o uso de GETDATE() em todo o banco de dados

1. Use a ferramenta SQL Server para exportar a definição de objetos de banco de dados para um arquivo SQL que deve incluir: tabelas, exibições, gatilhos, SPs, funções e assim por diante

2. Edite o arquivo SQL (faça um backup primeiro) usando qualquer editor de texto que permita encontrar o texto "GETDATE()"e substituí-lo por"[dbo].[getlocaldate]()"

3. Execute o arquivo SQL editado no Azure SQL para criar seus objetos de banco de dados...

4. Execute a migração de dados.

Aqui você tem uma referência da documentação do Azure: Gerando Scripts para SQL Azure

Qual seria a melhor maneira de implementar essa mudança?

Eu trabalharia ao contrário. Converta todos os seus carimbos de data/hora no banco de dados para UTC, use apenas UTC e siga o fluxo. Se você precisar de um carimbo de data/hora em um tz diferente, poderá criar uma coluna gerada usando AT TIME ZONE(como fez acima) que renderiza o carimbo de data/hora nesse TZ especificado (para o aplicativo). Mas, eu consideraria seriamente apenas ter o UTC retornado ao aplicativo e escrever essa lógica - a lógica de exibição - no aplicativo.

Em vez de exportar, editar manualmente e executar novamente, você pode tentar fazer o trabalho diretamente no banco de dados com algo como:

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

é claro, estendendo-o para lidar com funções, gatilhos e assim por diante também.

Existem algumas ressalvas:

• Você pode precisar ser um pouco mais brilhante e lidar com espaços em branco diferentes/extras entre CREATEe PROCEDURE/ VIEW/ <other>. Em vez do REPLACEpara isso, você pode preferir deixar o CREATEno lugar e executar um DROPprimeiro, mas isso corre o risco de deixar sys.dependse amigos fora de ordem onde ALTERnão podem, também se ALTERfalhar, você pelo menos tem o objeto existente ainda no lugar, onde com DROP+ CREATEvocê pode não.

• Se o seu código tiver algum cheiro "inteligente" como modificar seu próprio esquema com TSQL ad-hoc, você precisará garantir que a pesquisa e a substituição por CREATE-> ALTERnão interfiram com isso.

• Você vai querer testar a regressão de todo o(s) aplicativo(s) após a operação, quer use o cursor ou os métodos export+edit+run.

Eu usei esse método para fazer atualizações semelhantes em todo o esquema no passado. É um pouco hack e parece bastante feio, mas às vezes é a maneira mais fácil/rápida.

Padrões e outras restrições também podem ser modificados de forma semelhante, embora eles só possam ser descartados e recriados em vez de alterados. Algo como:

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

Um pouco mais de diversão com as quais você pode precisar lidar: se você estiver particionando por tempo, essas partes também precisam ser alteradas. Embora o particionamento no tempo mais granular do que no dia seja raro, você pode ter problemas em que DATETIMEs são interpretados pela função de particionamento como sendo o dia anterior ou seguinte, dependendo do timezine, deixando suas partições desalinhadas com suas consultas usuais.

Eu realmente gosto da resposta de David e aprovo isso para uma maneira programática de fazer as coisas.

Mas você pode tentar isso hoje para um teste no Azure via SSMS:

Clique com o botão direito do mouse em seu banco de dados -> Tarefas -> Gerar scripts.

[Back Story] tivemos um DBA júnior que atualizou todos os nossos ambientes de teste para o SQL 2008 R2 enquanto nossos ambientes de produção estavam no SQL 2008. É uma mudança que me faz estremecer até hoje. Para migrar para produção, de teste, tivemos que gerar scripts dentro do SQL, usando gerar scripts, e nas opções avançadas usamos a opção 'Tipo de dados para script: Esquema e dados' para gerar um arquivo de texto massivo. Conseguimos mover com sucesso nossos bancos de dados R2 de teste para nossos servidores SQL 2008 legados -- onde uma restauração de banco de dados para uma versão inferior não teria funcionado. Usamos o sqlcmd para inserir o arquivo grande - pois os arquivos geralmente eram grandes demais para o buffer de texto do SSMS.

O que estou dizendo aqui é que essa opção provavelmente funcionaria para você também. Você só precisará fazer uma etapa adicional e pesquisar e substituir getdate() por [dbo].getlocaldate no arquivo de texto gerado. (Eu colocaria sua função no banco de dados antes da migração).

(Eu nunca quis ser proficiente nesse curativo de restauração de banco de dados, mas por um tempo se tornou uma maneira de fato de fazer as coisas. E funcionou todas as vezes.)

Se você escolher esta rota, certifique-se e selecione o botão Avançado e selecione todas as opções que você precisa (leia cada uma) para mover do banco de dados antigo para o novo banco de dados - como os padrões que você mencionou. Mas faça alguns testes no Azure. Aposto que você descobrirá que esta é uma solução que funciona -- com um mínimo de esforço.

Altere dinamicamente todos os proc e udf para alterar o valor

    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

Observe comentado sysobjects Type column condition.Meu script alterará apenas proc e UDF.

Este script irá alterar tudo o Default Constraintque contémGetDate()

    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   

Eu votei na resposta de Evan Carrolls, pois acho que essa é a melhor solução. Não consegui convencer meus colegas de que eles deveriam mudar muito código C#, então tive que usar o código que David Spillett escreveu. Corrigi alguns problemas com UDFs, SQL dinâmico e esquemas (nem todos os códigos usam "dbo.") assim:

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

e as restrições padrão como esta:

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
A sugestão de usar uma UDF que retorne a data e hora de hoje parece legal, mas acho que ainda há problemas de desempenho suficientes com UDFs, então optei por usar a solução AT TIME ZONE muito longa e feia.