Regular Expressions in SQL Server

In my last post ddl triggers as work-around for policy limitations, I discussed how triggers can be used to enforce database settings are not changed. This cannot be achieved through Policy Based Management, because the Database facet does not support On Change: Log or On Change: Prevent modes.

I also noted that I was not happy with the pattern matching that I originally used within the trigger, to resolve the issue. Therefore, in this post, I will talk about how we can use CLR to gain the benefits of regular expressions in SQL Server.

So, the challenge is....We want a trigger that will ensure that no statement can turn off the AUTO CREATE STATISTICS, AUTO UPDATE STATISTICS or AUTO UPDATE STATISTICS ASYNC  settings of any database within the instance. The pattern matching offered by the LIKE predicate in T-SQL works well for changes made through the GUI, but if a change is scripted, then a developer may have used an extra space, a tab, or a line break, etc.

The first thing that we need to do to work around this, is create a CLR function, that will accept the SQL Statement from the EVENTDATA() function as a parameter and check the patter against the regex libraries. Below is the c# method to achieve this.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static int CheckMatch(SqlString statement)

    {

        Regex rgxUpdate = new Regex("AUTO_UPDATE_STATISTICS[_ASYNC]{0,}[\r\n\t{0,}[ \n\t\t]{0,}OFF", RegexOptions.IgnoreCase | RegexOptions.Singleline);

        Int16 retcounter = 0;

        string stringstatement = Convert.ToString(statement);

        Match matchUpdate = rgxUpdate.Match(stringstatement);

        if (matchUpdate.Success)

        {

            retcounter++;

        }

        Regex rgxCreate = new Regex("AUTO_CREATE_STATS[\r\n\t{0,}[ \n\t\t]{0,}OFF", RegexOptions.IgnoreCase | RegexOptions.Singleline);

        Match matchCreate = rgxCreate.Match(stringstatement);

        if (matchCreate.Success)

        {

            retcounter++;

        }

        if (retcounter > 0)

        { return 1; }

        else

        { return 0; }

    }

}

There are a few things to pay particular attention to in this code. Firstly, notice that we are importing the following .NET base libraries:

System.Data.SqlTypes

Microsoft.SqlServer.Server

System.Text.RegularExpressions

The first of these libraries, is used because we are working with the sqlstring data type, The second library is required because we are creating a function that will be used by SQL Server and the third library is required to use the regex classes.

The next thing to note in this code is the regex options. We are using regex options to treat the SQL Statement as a single line and to make the pattern match case insensitive.

Once we have built the project, we will need to create an assembly within SQL Server. This can be created using the CREATE ASSEMBLY DDL statement, as demonstrated below.

CREATE ASSEMBLY CheckStats   
FROM 'c:\sql_assemblies\checkstats.dll'  
WITH PERMISSION_SET = SAFE; 

The script assumes that we have built the project as CheckStats.dll to the c:\sqlassemblies folder.

Now that the assembly has been built, we can create the CLR function, which will call the c# method within the assembly. The script below demonstrates how to create the function.

CREATE FUNCTION dbo.CheckMatch

(

@Statement NVARCHAR(MAX)

)

RETURNS INT

AS EXTERNAL NAME CheckStats.UserDefinedFunction.CheckMatch;

Notice that we reference the method, using the 3-part name Assembly.Class.Method

Finally, we can create the server scoped DDL trigger that will respond to ALTER DATABASE statements and call the CLR function. This is demonstrated below.

CREATE TRIGGER StatisticsManagement

ON ALL SERVER

AFTER ALTER_DATABASE

AS

BEGIN

IF (SELECT dbo.CheckMatch(UPPER(EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')))) = 1

BEGIN

PRINT 'Auto Stats Settings MUST Be Configured As ON';

ROLLBACK;

        END

END

You can read more about DDL Triggers in my book Pro SQL Server Administration, available from apress.com, Amazon and most book shops.