CODEWITHSUNDEEP
c#sql.netregex
mr.coffee
mr.coffee

Reputation: 1028

.NET Regex find all SQL INSERT and Update with GETDATE

My company is moving our SQL databases to the cloud (Azure and AWS depending on client/region) so SQL server local datetime is no longer meaningful and we want to use UTC datetime instead. I need to identify all database objects (mostly Stored Procedures and functions) that are using GETDATE(). I have to automate this process because each database contains thousands of objects and we have many databases to migrate. Initially we are only applying this change to CreatedDate and ModifiedDate columns present on most tables for auditing purposes.

Step by step:

  1. (done) Identify all tables containing CreatedDate and ModifiedDate columns
  2. (done) Identify all SQL objects that reference tables found in step 1
  3. Regex each reference to see if it is updating or inserting into the table with GETDATE()

Step 3 is where I am stuck. The INSERT and UPDATE statements are inconsistently formatted for each database because the systems have been developed over a couple decades by many developers. I have a regex that is partially working but is failing to identify some multiline statements.

I am beginner-level with Regex so to make this easier on myself I broke the Regex into multiple steps. I am using RegexOptions.IgnoreCase in .NET for all operations.

The first step is to identify if the reference contains an INSERT or UPDATE. This regex is working well and matches all examples in my test input.

(insert\s*into|update)\s*(MySchema.MyTable|MySchema.\[MyTable\]|\[MySchema].MyTable|\[MySchema\].\[MyTable\])

The second step is to check if the INSERT or UPDATE statement is using GETDATE(). My Regex is partially working but fails to identify multiline statements. What am I doing wrong here?

values\s*\((\s|.*)getdate()(\s|.*)\)|SET\s*.*=\s*getdate\(\)

The second problem I have is combining these two regex statements into one separated with \s*.*. Combining the two statements breaks some functionality that was previously working for each regex statement independently.

(insert\s*into|update)\s*(MySchema.MyTable|MySchema.\[MyTable\]|\[MySchema].MyTable|\[MySchema\].\[MyTable\])\s*.*values\s*\((\s|.*)getdate()(\s|.*)\)|SET\s*.*=\s*getdate\(\)

My sample input to reproduce results:

INSERT INTO [MySchema].[MyTable] (ID, ObjectValue, ObjectData, Col4, CreatedDate, CreatedBy, LastModified, LastModifiedBy) VALUES (3, 'Hello', 'World', 12.32, GETDATE(), @CreatedBy, GETDATE(), @CreatedBy)

INSERT INTO MySchema.MyTable (ID, ObjectValue, ObjectData, Col4, CreatedDate, CreatedBy, LastModified, LastModifiedBy)
    VALUES (3, 'Hello', 'World', 12.32, GETDATE(), @CreatedBy, GETDATE(), @CreatedBy)
    
INSERT INTO
    MySchema.MyTable
        (ID, ObjectValue, ObjectData, Col4, CreatedDate, CreatedBy, LastModified, LastModifiedBy)
    VALUES
        (3, 'Hello', 'World', 12.32, GETDATE(), @CreatedBy, GETDATE(), @CreatedBy)
        
INSERT INTO MySchema.[MyTable] (ID, 
    ObjectValue, 
    ObjectData, 
    Col4, 
    CreatedDate, 
    CreatedBy, 
    LastModified, 
    LastModifiedBy)
VALUES  (3, 'Hello', 'World', 12.32, GETDATE(), @CreatedBy, GETDATE(), @CreatedBy)

INSERT INTO [MySchema].MyTable (ID , ObjectValue, ObjectData, Col4, CreatedDate, CreatedBy, LastModified, LastModifiedBy)
VALUES (
    3, 
    'Hello', 
    'World', 
    12.32, 
    GETDATE(), 
    @CreatedBy, 
    GETDATE(), 
    @CreatedBy
)

UPDATE [MySchema].[MyTable] SET ID = 3, ObjectValue = 'Hello', ObjectData = 'World', Col4 = 12.32, LastModified = GETDATE(), LastModifiedBy = @LastModifiedBy

UPDATE MySchema.MyTable
SET ID = 3, ObjectValue = 'Hello', ObjectData = 'World', Col4 = 12.32, LastModified = GETDATE(), LastModifiedBy = @LastModifiedBy

UPDATE
    MySchema.MyTable
SET
    ID = 3, 
    ObjectValue = 'Hello', 
    ObjectData = 'World', 
    Col4 = 12.32, 
    LastModified = GETDATE(), 
    LastModifiedBy = @LastModifiedBy
    
UPDATE MySchema.[MyTable] SET ID = 3, ObjectValue = 'Hello', ObjectData = 'World', 
Col4 = 12.32, LastModified = GETDATE(), LastModifiedBy = @LastModifiedBy

UPDATE [MySchema].MyTable
    SET ID = 3, ObjectValue = 'Hello', ObjectData = 'World', Col4 = 12.32, LastModified = GETDATE(), LastModifiedBy = @LastModifiedBy

Upvotes: 0

Views: 94

Answers (1)

Caius Jard
Caius Jard

Reputation: 74680

SQL server local datetime is no longer meaningful and we want to use UTC datetime instead

Azure uses UTC; put your database on Azure and GetDate() will start returning UTC times.

There are no code changes needed; on Azure both GetDate() and GetUtcDate() return the UTC time

Happy to keep working through your mission to switch over to using GetUtcDate, but I did want to point out it's not strictly necessary..

Upvotes: 1

Related Questions