SQL SERVER 2008R2 CLR 正则表达式替换

希望能在看到一个存储过程中引用了哪些表，但又不希望看到在存储过程中已经被注释的那些。 由于SQL Server 对正则表达式的处理能力有限。我也尝试了一些方法，没找到合适的。

http://bbs.csdn.net/topics/390728045

所以只好使用CLR试试，效果不错。

代码如下。

第一步，创建CLR 的DLL 文件。 这个过程大家可以参考这个文章

C#代码如下。注意事项是，由于我的数据库是SQL SERVER 2008 R2，所以创建项目时，要指定项目的framework 是.net 3.5 不然后面会有一些兼容错误。

//assembly should be created in .net 3.5 for SQL SERVER 2008R2
//c#CODE AS BELOW
using System.Text;   
using Microsoft.SqlServer.Server;   
using System.Data.SqlTypes;   
using System.Text.RegularExpressions;   
   
public partial class SQLReg   
{       
    [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]   
    public static SqlString fn_RegexReplace(   
        SqlString input, SqlString pattern, SqlString replacement)   
    {   
        return (SqlString)Regex.Replace(   
            input.Value, pattern.Value, replacement.Value);   
    }   
}   

要在数据库中使用CLR，得在相应的数据库上设置如下选项。

EXEC sp_configure 'clr enable', 1;
RECONFIGURE;
GO

设置好之后，创建assembly，DLL是由上面的步骤获得的。

CREATE ASSEMBLY SQLReg
FROM 'C:\Temp\SQLRegexReplace.dll';
GO

创建成功后，在SQL SERVER 数据库的Programmability下的Assemblies可以看到SQLReg。 我们可以把Assembly 以脚本的形式导出，这样就可以在其他数据库运行这个脚本，来使用Assembly中的函数，当然这个数据库得设置了‘clr enable’。

本文的Assembly代码如下：

/****** Object: SqlAssembly [SQLReg] Script Date: 04/16/2014 21:12:57 ******/
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'SQLReg' and is_user_defined = 1)
DROP ASSEMBLY [SQLReg]
GO
/****** Object: SqlAssembly [SQLReg] Script Date: 04/16/2014 21:12:57 ******/

--
CREATE ASSEMBLY [SQLReg]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001C7D4E530000000000000000E00002210B010800000A00000006000000000000DE2800000020000000400000000040000020000000020000040000000000000004000000000000000080000000020000000000000300408500001000001000000000100000100000000000001000000000000000000000008428000057000000004000003803000000000000000000000000000000000000006000000C000000DC2700001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000E408000000200000000A000000020000000000000000000000000000200000602E72737263000000380300000040000000040000000C0000000000000000000000000000400000402E72656C6F6300000C0000000060000000020000001000000000000000000000000000004000004200000000000000000000000000000000C02800000000000048000000020005008C200000500700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000133003002500000001000011000F00281100000A0F01281100000A0F02281100000A281200000A281300000A0A2B00062A1E02281400000A2A00000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000030020000237E00009C0200001803000023537472696E677300000000B40500000800000023555300BC050000100000002347554944000000CC0500008401000023426C6F620000000000000002000001471502000900000000FA2533001600000100000014000000020000000200000003000000140000000E00000001000000010000000300000000000A0001000000000006003C0035000A0064004F000600B500A3000600CC00A3000600E900A30006000801A30006002101A30006003A01A30006005501A30006007001A3000600A80189010600BC0189010600CA01A3000600E301A3000600130200023F00270200000600560236020600760236020A00BF02A4020E00FD02DE020000000001000000000001000100010010001E00000005000100010050200000000096006E000A0001008120000000008618830015000400000001008900000002008F00000003009700190083001900210083001900290083001900310083001900390083001900410083001900490083001900510083001900590083001E006100830019006900830019007100830019007900830023008900830029009100830015009900830015001100D402D200A1000303D60011000B03DD00090083001500200083002E002E00330003012E001300FD002E001B00FD002E002300FD002E002B00E8002E000B00E8002E003B00FD002E004B00FD002E0053001B012E00630045012E006B0052012E0073005B012E007B006401E3000480000001000000000000000000000000009402000002000000000000000000000001002C00000000000200000000000000000000000100430000000000020000000000000000000000010035000000000000000000003C4D6F64756C653E0053514C52656765785265706C6163652E646C6C0053514C5265675265706C616365006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C537472696E670067665F7379735F52656765785F5265706C616365002E63746F7200696E707574007061747465726E007265706C6163656D656E740053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C747572654174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C65417474726962757465004775696441747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053514C52656765785265706C616365004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E417474726962757465006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E73005265676578005265706C616365006F705F496D706C6963697400000003200000000000BADADA0ADCF27C4BBF780536818184620008B77A5C561934E0890A0003110911091109110903200001042001010E0420010102052001011141042001010880A20100020054020F497344657465726D696E6973746963015455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D322E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A44617461416363657373000000000320000E0600030E0E0E0E05000111090E04070111091401000F53514C52656765785265706C616365000005010000000017010012436F7079726967687420C2A920203230313400002901002436656162343632612D613662322D346437362D623563652D62313939376332656536336200000C010007312E302E302E3000000801000701000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000001C7D4E5300000000020000008B000000F8270000F8090000525344537925AAAAD6F0D94B913DB4BA8E723FB801000000633A5C75736572735C707A6E63755C646F63756D656E74735C76697375616C2073747564696F20323031305C50726F6A656374735C53514C52656765785265706C6163655C53514C52656765785265706C6163655C6F626A5C44656275675C53514C52656765785265706C6163652E7064620000AC2800000000000000000000CE280000002000000000000000000000000000000000000000000000C02800000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000E00200000000000000000000E00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00440020000010053007400720069006E006700460069006C00650049006E0066006F0000001C0200000100300030003000300030003400620030000000480010000100460069006C0065004400650073006300720069007000740069006F006E0000000000530051004C00520065006700650078005200650070006C006100630065000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000048001400010049006E007400650072006E0061006C004E0061006D0065000000530051004C00520065006700650078005200650070006C006100630065002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100340000005000140001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C00520065006700650078005200650070006C006100630065002E0064006C006C000000400010000100500072006F0064007500630074004E0061006D00650000000000530051004C00520065006700650078005200650070006C006100630065000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E00300000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C000000E03800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE
GO

下面我们创建一个自定义函数来使用Assembly中的函数。代码如下

--SQLReg assembly name
--SQLRegReplace class name
--gf_sys_Regex_Replace function in class
--
CREATE FUNCTION global.gf_sys_REGEX_REPLACE(
  @input AS NVARCHAR(MAX),
  @pattern AS NVARCHAR(MAX),
  @replacement AS NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME SQLReg.SQLRegReplace.gf_sys_Regex_Replace;
GO

--测试代码
select global.gf_sys_REGEX_REPLACE('<div>aaaa</div>','<[^>]+>','')

下面的代码就是将存储过程定义语句，进行消去注释 的处理，注意的是，如果存储过程的定义进行了加密处理，那么这里是无法处理这类存储过程。

我将存储过程定义按行存储到一个表中，这样方便后面和另一个表做连接，来确定这个存储过程中引用了哪些数据表。

代码如下

SET NOCOUNT ON  
  
DROP TABLE TEMP_TABLE;  
  
CREATE TABLE TEMP_TABLE  
  (  
     SPNAME  NVARCHAR(200),  
     ROWSDEF NVARCHAR(MAX)  
  )  
  
DECLARE @vSP_Name          NVARCHAR(200)=N'',  
        @vSP_Definition    NVARCHAR(max)=N'',  
        @vSP_DefinitionTmp NVARCHAR(max)=N''  
--extract all the store procedure's definition.
DECLARE curStoreProcedureDefinition CURSOR FOR  
  SELECT OBJECT_NAME(SM.OBJECT_ID) AS SP_Name,  
         --replace(DEFINITION, '''', '''''') AS SP_Definition,  
         DEFINITION                AS SP_Definition  
  FROM   SYS.SQL_MODULES SM  
  JOIN   SYS.OBJECTS SO  
    ON SO.OBJECT_ID = SM.OBJECT_ID  
  WHERE  SO.TYPE = 'P' AND OBJECT_NAME(SM.OBJECT_ID) LIKE '%service_health%'  
  
OPEN curStoreProcedureDefinition;  
  
FETCH NEXT FROM curStoreProcedureDefinition INTO @vSP_Name, @vSP_Definition  
  
WHILE @@FETCH_STATUS = 0  
  BEGIN  
      DECLARE @flag INT=1  
      DECLARE @flag3 INT=1  
      
      --PRINT @vSP_Name  
      --PRINT  @vSP_Definition  
      SET @vSP_DefinitionTmp=@vSP_Definition  
	  --here still need to be improved to handle the nested comments
      SET @vSP_DefinitionTmp=global.gf_sys_REGEX_REPLACE (@vSP_DefinitionTmp,'\/\*((?!(\/\*|\*\/))[\s\S])*?\*\/|--.*\n','')   
       
      --SET @vSP_DefinitionTmp=global.gf_sys_REGEX_REPLACE (@vSP_DefinitionTmp,'\-\-([^\''\r\n]{0,}(\''[^\''\r\n]{0,}\''){0,1}[^\''\r\n]{0,}){0,}$','')   
       
        PRINT @vSP_DefinitionTmp  
      
      --PRINT @vSP_DefinitionTmp  
  
      --set @vSP_DefinitionTmp=@vSP_Definition  
      WHILE @flag3 > 0  
        BEGIN  
            SET @flag3=charindex(char(10), @vSP_DefinitionTmp)  
  
            --print @flag3  
			
            IF len(ltriM(rtrim(LEFT(@vSP_DefinitionTmp, charindex(char(10), @vSP_DefinitionTmp))))) >= 5  
              INSERT INTO TEMP_TABLE  
              VALUES      (@vSP_Name,  
                           LTRIM(RTRIM(LEFT(@vSP_DefinitionTmp, charindex(char(10), @vSP_DefinitionTmp)))))  
  
            IF len(@vSP_DefinitionTmp) - charindex(char(10), @vSP_DefinitionTmp) > 0  
              SET @vSP_DefinitionTmp= RIGHT(@vSP_DefinitionTmp, len(@vSP_DefinitionTmp) - charindex(char(10), @vSP_DefinitionTmp))  
            ELSE  
              SET @vSP_DefinitionTmp=''  
        --print @vSP_Definition  
        END  
  
      FETCH NEXT FROM curStoreProcedureDefinition INTO @vSP_Name, @vSP_Definition  
  END  
  
CLOSE curStoreProcedureDefinition;  
  
DEALLOCATE curStoreProcedureDefinition;  
  
SELECT *  
FROM   TEMP_TABLE