用正则提取SQL脚本关键字屏蔽常见错误

      每天的批量部署SQL script难免会出现一些小差错，为了避免某些常见而又容易忽略的错误的出现，我们在执行前先将脚本过滤一遍，通过一些关键字来提示是否出现了不符合的设置。

      本实例以创建表和给表怎加列不容许出现null列以及在执行脚本过程中不容许出现设置SET ANSI_PADDING OFF为例，利用正则获取脚本中的关键字，以检索是否符合要求。

      完成这个任务我们的主要挑战是如何获取sql 脚本中的关键字，如创建新table时，我们如何判断出那一段脚本是字段列，这里就需要用的正则来提取出关键字。来看看通常创建表的脚本：

CREATE TABLE [dbo].[A](
	[EmployerID] [int] NOT NULL,
	[InvoiceItemID] [int] NOT NULL
) ON [PRIMARY]

GO

　　像这样的语句结构我们只需要提取出“CREATE TABLE [dbo].[A](” 与“) ON [PRIMARY] GO”之间的语句然后用‘，’分割出每个字段，即可判断是否有“NOT NULL”关键字。

      但是事与愿违，我们穿件一个表可能面临着更多的情况，比如需要设置主键：

create table Ace 
(
AceID int,
AceName nvarchar(20),
CONSTRAINT [PK_Billing_SalesInvoice_Item] PRIMARY KEY CLUSTERED 
(
	AceID ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

　　很明显这个脚本中，我们要提取的就是“Create table Ace (” 与 “CONSTRAINT” 之间的内容了。所以处理起来就需要分情况而定。下面来看我的处理：

        /// <summary>
        /// Check exists "NOT NULL" when create table.
        /// </summary>
        /// Author: Ace Yue
        /// Date:2013-04-08
        /// <param name="sqlScript">the sql script</param>
        protected void CheckNullableWhenCreate(string sqlScript)
        {
            string sCreateBegin = @"\s?create\s+table\s+((dbo.)?|(\[dbo\].)?)(\[?)([a-z]+[_,a-z0-9]*(\]?))(\s*)(\()(\s*)";//Get the script start when create new table.
            Regex regCheck = new Regex(sCreateBegin);
            if (regCheck.IsMatch(sqlScript))
            {
                int iSplitStart = sqlScript.IndexOf(regCheck.Match(sqlScript).Value);
                int iSplitEnd = 0;
                string sSplit = sqlScript.Substring(iSplitStart + regCheck.Match(sqlScript).Value.Length);//Capture the script behind the table's name.
                string sEndGeneral = @"(\s*)(?<![0-9])(\){1})";// get the ")" but not in "(10)"
                string sEndSpecific = @"(\,\s*)(constraint)(\s+)((\[?)[a-z]+[_,a-z0-9]*(\]?))(\s+)(primary)(\s+)(key)(\s+)(clustered)"; //Get the "CONSTRAINT" when the script contain "CONSTRAINT".
                Regex regEndGeneral = new Regex(sEndGeneral);
                Regex regEndSpecific = new Regex(sEndSpecific);

                if (regEndSpecific.IsMatch(sSplit))
                    iSplitEnd = sSplit.IndexOf(regEndSpecific.Match(sSplit).Value);
                else if (regEndGeneral.IsMatch(sSplit))
                    iSplitEnd = sSplit.IndexOf(regEndGeneral.Match(sSplit).Value);
                else
                    iSplitEnd = sSplit.Length;
                sSplit = sSplit.Substring(0, iSplitEnd);

                string[] arrScript = sSplit.Split(',');
                string sExistsNotNull = @"(\s+)(not)(\s+)(null)(\s*)";
                Regex regExistsNotNull = new Regex(sExistsNotNull);
                foreach (string sItem in arrScript)
                {
                    if (!regExistsNotNull.IsMatch(sItem))
                    {
                        this.bPass = false;
                        this.SReturn += "The SQL script not contain \"NOT NULL\" in some column when create table. Do you want continue ? \r\n";
                    }
                }
            }
        }

　　下面来看看Alter表的情况：

ALTER TABLE dbo.Billing_Plan_Payment_Bank_Log
      Add InvoiceReference1 NVarChar(100)    NOT NULL CONSTRAINT
DF_Billing_Plan_Payment_Bank_Log_InvoiceReference1 DEFAULT '',
            InvoiceReference2 NVarChar(100)    NOT NULL CONSTRAINT
DF_Billing_Plan_Payment_Bank_Log_InvoiceReference2 DEFAULT '',
            InvoiceReference3 NVarChar(100)    NOT NULL CONSTRAINT
DF_Billing_Plan_Payment_Bank_Log_InvoiceReference3 DEFAULT ''
Go

　　处理的方面与上述情况类似，我们需要提取想要的东西就可以了：

        /// <summary>
        /// Check the script exists "NOT NULL" when Alter table add columns.
        /// </summary>
        /// Author: Ace Yue
        /// Date:2013-04-08
        /// <param name="sqlScript">the sql script</param>
        protected void CheckNullableWhenAlter(string sqlScript)
        {
            string sAlterBegin = @"(\s*)(alter)(\s+)(table)(\s+)((dbo.)?|(\[dbo\].)?)(\[?)([a-z]+[_,a-z0-9]*(\]?))(\s+)(add)(\s+)";// Get the script begin when alter table. like "ALTER TABLE dbo.Billing_Plan_Payment_Bank_Log Add"
            Regex regCheck = new Regex(sAlterBegin);
            if (regCheck.IsMatch(sqlScript))
            {
                int iSplitStart = sqlScript.IndexOf(regCheck.Match(sqlScript).Value);
                string sSplit = sqlScript.Substring(iSplitStart + regCheck.Match(sqlScript).Value.Length);
                string sAlterEnd = @"(\s+)((go\s+)|(go$))";
                Regex regEnd = new Regex(sAlterEnd);
                int iSplitEnd = 0;
                if (regEnd.IsMatch(sSplit))
                    iSplitEnd = sSplit.IndexOf(regEnd.Match(sSplit).Value);
                else
                    iSplitEnd = sSplit.Length;
                sSplit = sSplit.Substring(0, iSplitEnd);

                string[] arrScript = sSplit.Split(',');
                string sExistsNotNull = @"(\s+)(not)(\s+)(null)(\s*)";
                Regex regExistsNotNull = new Regex(sExistsNotNull);
                foreach (string sItem in arrScript)
                {
                    if (!regExistsNotNull.IsMatch(sItem))
                    {
                        this.bPass = false;
                        this.SReturn += "The SQL script not contain \"NOT NULL\" in some column when add new column. Do you want continue ? \r\n";
                    }
                }
            }
        }

　　处理SET ANSI_PADDING OFF 这个就是太简单了，主需要查出就可以了：

        /// <summary>
        /// Check the Script is contain "Set ANSI OFF" when 
        /// </summary>
        /// Author: Ace Yue
        /// Date:2013-04-08
        /// <param name="sqlScript">The SQL Script whose you want to check</param>
        protected void CheckANSI(string sqlScript)
        {
            Regex reg = new Regex(@"set\s+ansi_padding{1}\s+(off\s+|off$)");// Is there have "SET ANSI_PADDING OFF" 
            if (reg.IsMatch(sqlScript))
            {
                this.BPass = false;
                this.SReturn += "This SQL script contain \"SET ANSI_PADDING OFF\". Do you want to continue?\r\n";
            }
        }

　　到了这里我们的任务就完成了，下面是我的完整Demo：

//==============================================================
// Copyright (C) Moonplus.net Corporation . Ace Yue 2013-04-08
//==============================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Text.RegularExpressions;

namespace SQLScript
{
    /// <summary>
    /// Define check the SQL Script delegate.
    /// </summary>
    /// <param name="sqlScrit">The SQL Script.</param>
    public delegate void CheckScriptDel(string sqlScrit);

    public class ScriptCheckHelper
    {
        private bool bPass = true;
        public bool BPass { get { return bPass; } set { this.bPass = value; } }

        private string sReturn = "";
        public string SReturn { get { return sReturn; } set { this.sReturn = value; } }

        
        /// <summary>
        /// Start invoke the check script function.
        /// </summary>
        /// Author: Ace Yue
        /// Date: 2013-04-08
        /// Mantis:0033628: SIS 3.0 - Research - Build a small SQL script validation tools to Check SQL issues 
        /// <param name="sqlScript">The SQL Script whose you want to check</param>
        public void StartCheck(string sqlScript)
        {
            CheckScriptDel checkDel = CheckNullable;
            checkDel += CheckANSI;
            checkDel(sqlScript.ToLower());
        }

        /// <summary>
        /// Check the Script is contain the nullable field when create table or add new field.
        /// </summary>
        /// <param name="sqlScirpt">The SQL Script whose you want to check</param>
        protected void CheckNullable(string sqlScirpt)
        {
            CheckScriptDel checkNullable = CheckNullableWhenCreate;
            checkNullable += CheckNullableWhenAlter;
            checkNullable(sqlScirpt);
        }

        /// <summary>
        /// Check the Script is contain "Set ANSI OFF" when 
        /// </summary>
        /// <param name="sqlScript">The SQL Script whose you want to check</param>
        protected void CheckANSI(string sqlScript)
        {
            Regex reg = new Regex(@"set\s+ansi_padding{1}\s+(off\s+|off$)");// Is there have "SET ANSI_PADDING OFF" 
            if (reg.IsMatch(sqlScript))
            {
                this.BPass = false;
                this.SReturn += "This SQL script contain \"SET ANSI_PADDING OFF\". Do you want to continue?\r\n";
            }
        }

        /// <summary>
        /// Check exists "NOT NULL" when create table.
        /// </summary>
        /// <param name="sqlScript">the sql script</param>
        protected void CheckNullableWhenCreate(string sqlScript)
        {
            string sCreateBegin = @"\s?create\s+table\s+((dbo.)?|(\[dbo\].)?)(\[?)([a-z]+[_,a-z0-9]*(\]?))(\s*)(\()(\s*)";//Get the script start when create new table.
            Regex regCheck = new Regex(sCreateBegin);
            if (regCheck.IsMatch(sqlScript))
            {
                int iSplitStart =sqlScript.IndexOf(regCheck.Match(sqlScript).Value);
                int iSplitEnd = 0;
                string sSplit = sqlScript.Substring(iSplitStart + regCheck.Match(sqlScript).Value.Length);//Capture the script behind the table's name.
                string sEndGeneral = @"(\s*)(?<![0-9])(\){1})";// get the ")" but not in "(10)"
                string sEndSpecific = @"(\,\s*)(constraint)(\s+)((\[?)[a-z]+[_,a-z0-9]*(\]?))(\s+)(primary)(\s+)(key)(\s+)(clustered)"; //Get the "CONSTRAINT" when the script contain "CONSTRAINT".
                Regex regEndGeneral = new Regex(sEndGeneral);
                Regex regEndSpecific = new Regex(sEndSpecific);

                if (regEndSpecific.IsMatch(sSplit))
                    iSplitEnd = sSplit.IndexOf(regEndSpecific.Match(sSplit).Value);
                else if (regEndGeneral.IsMatch(sSplit))
                    iSplitEnd = sSplit.IndexOf(regEndGeneral.Match(sSplit).Value);
                else
                    iSplitEnd = sSplit.Length;
                sSplit = sSplit.Substring(0, iSplitEnd);

                string[] arrScript = sSplit.Split(',');
                string sExistsNotNull = @"(\s+)(not)(\s+)(null)(\s*)"; 
                Regex regExistsNotNull =new Regex(sExistsNotNull);
                foreach (string sItem in arrScript)
                {
                    if (!regExistsNotNull.IsMatch(sItem))
                    {
                        this.bPass = false;
                        this.SReturn += "The SQL script not contain \"NOT NULL\" in some column when create table. Do you want continue ? \r\n";
                    }
                }
            }
        }

        /// <summary>
        /// Check the script exists "NOT NULL" when Alter table add columns.
        /// </summary>
        /// <param name="sqlScript">the sql script</param>
        protected void CheckNullableWhenAlter(string sqlScript)
        {
            string sAlterBegin = @"(\s*)(alter)(\s+)(table)(\s+)((dbo.)?|(\[dbo\].)?)(\[?)([a-z]+[_,a-z0-9]*(\]?))(\s+)(add)(\s+)";// Get the script begin when alter table. like "ALTER TABLE dbo.Billing_Plan_Payment_Bank_Log Add"
            Regex regCheck = new Regex(sAlterBegin);
            if (regCheck.IsMatch(sqlScript))
            {
                int iSplitStart = sqlScript.IndexOf(regCheck.Match(sqlScript).Value);
                string sSplit = sqlScript.Substring(iSplitStart + regCheck.Match(sqlScript).Value.Length);
                string sAlterEnd = @"(\s+)((go\s+)|(go$))";
                Regex regEnd = new Regex(sAlterEnd);
                int iSplitEnd=0;
                if (regEnd.IsMatch(sSplit))
                    iSplitEnd = sSplit.IndexOf(regEnd.Match(sSplit).Value);
                else
                    iSplitEnd = sSplit.Length;
                sSplit = sSplit.Substring(0, iSplitEnd);

                string[] arrScript = sSplit.Split(',');
                string sExistsNotNull = @"(\s+)(not)(\s+)(null)(\s*)";
                Regex regExistsNotNull = new Regex(sExistsNotNull);
                foreach (string sItem in arrScript)
                {
                    if (!regExistsNotNull.IsMatch(sItem))
                    {
                        this.bPass = false;
                        this.SReturn += "The SQL script not contain \"NOT NULL\" in some column when add new column. Do you want continue ? \r\n";
                    }
                }
            }
        }

    }
}

　　看看运行结果：

      由于正则表达式我是现学现卖，并不能保证对所有情况都考虑到，如果有遗漏欢迎提出来，谢谢！