x

search all instance for regex value sql server

Hi I asked this on stack overflow but no answer as yet... i'm trying to create a script that will allow me to use different regex to search for different values in dbs across an instance... i.e. phone numbers, card numbers etc. I think i've nearly cracked it I just need some help, i'm not sure the regex will work properly... pasted my code below... thanks!

 sp_configure 'clr enabled',1
 reconfigure
 
 DECLARE @SearchStr NVARCHAR(100)
 
 -- search for uk phone number for example   
 SET @SearchStr = '(((\+44)? ?(\(0\))? ?)|(0))( ?[0-9]{3,4}){3}'
 
 CREATE TABLE #Results
     (
       ColumnName NVARCHAR(370) ,
       ColumnValue NVARCHAR(3630)
     )
 
 SET NOCOUNT ON
 
 DECLARE @TableName NVARCHAR(256) ,
     @ColumnName NVARCHAR(128) ,
     @SearchStr2 NVARCHAR(110)
 SET @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')
 
 WHILE @TableName IS NOT NULL 
     BEGIN
         SET @ColumnName = ''
         SET @TableName = ( SELECT   MIN(QUOTENAME(TABLE_SCHEMA) + '.'
                                         + QUOTENAME(TABLE_NAME))
                            FROM     INFORMATION_SCHEMA.TABLES
                            WHERE    TABLE_TYPE = 'BASE TABLE'
                                     AND QUOTENAME(TABLE_SCHEMA) + '.'
                                     + QUOTENAME(TABLE_NAME) > @TableName
                                     AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA)
                                                               + '.'
                                                               + QUOTENAME(TABLE_NAME)),
                                                        'IsMSShipped') = 0
                          )
 
 
         WHILE ( @TableName IS NOT NULL )
             AND ( @ColumnName IS NOT NULL ) 
             BEGIN
 
                 SET @ColumnName = ( SELECT  MIN(QUOTENAME(COLUMN_NAME))
                                     FROM    INFORMATION_SCHEMA.COLUMNS
                                     WHERE   TABLE_SCHEMA = PARSENAME(@TableName,
                                                               2)
                                             AND TABLE_NAME = PARSENAME(@TableName,
                                                               1)
                                             AND DATA_TYPE IN ( 'char',
                                                               'varchar',
                                                               'nchar',
                                                               'nvarchar',
                                                               'int', 'decimal' )
                                             AND QUOTENAME(COLUMN_NAME) > @ColumnName
                                   )
 
 
 
                 IF @ColumnName IS NOT NULL 
                     BEGIN
 
                         INSERT  INTO #Results
                                 EXEC
                                     ( 'SELECT ''' + @TableName + '.'
                                       + @ColumnName + ''', LEFT('
                                       + @ColumnName + ', 3630) FROM '
                                       + @TableName + ' (NOLOCK) ' + ' WHERE '
                                       + @ColumnName + ' LIKE ' + @SearchStr2
                                     )
 
                     END
 
             END   
 
     END
 
 
 
 SELECT  ColumnName ,
         ColumnValue
 FROM    #Results
 
 
 
 DROP TABLE #Results
 
 --sp_configure 'clr enabled',0
 --reconfigure
more ▼

asked Oct 17, 2011 at 04:35 AM in Default

avatar image

jhowe
1.1k 56 60 66

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The code you have posted will look into the same table x times where x = number of columns in a table and I bet that all of your columns is not indexed or the indexes can't be used. That means that you'll get x table scans for y tables. Thats a lot of work.

You probably want a single scan/seek. Try to create a concatenated string and execute the string only once per table.

more ▼

answered Oct 17, 2011 at 04:48 AM

avatar image

Håkan Winther
16.4k 36 45 57

Don't quite understand what you mean, or how I would implement that... would you be able to edit my code or provide an example? Thanks for your help...

Oct 17, 2011 at 06:21 AM jhowe
(comments are locked)
10|1200 characters needed characters left

You can't use regex expressions like that in TSQL.

You would have to create a regex function in CLR - search here for questions about regex - you will get a lot of good answers http://ask.sqlservercentral.com/search/?q=regex&Submit=search&t=question

As Håkan has said, this isn't going to run nicely, but as it stands, it isn't going to run at all!

more ▼

answered Oct 17, 2011 at 06:50 AM

avatar image

Kev Riley ♦♦
63.8k 48 61 81

I haven't tried it myself, but if you install Master data services, you'll get support for regex. I don't know the syntax, but you can try http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Oct 17, 2011 at 06:58 AM Håkan Winther

Yes these are CLR functions supplied with MDS, whether or not you can lift and use them in non-MDS is another matter, but essentially it's the same as writing your own. There still is no TSQL support for regex.

Oct 17, 2011 at 07:31 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2072
x1066

asked: Oct 17, 2011 at 04:35 AM

Seen: 1052 times

Last Updated: Oct 17, 2011 at 04:36 AM

Copyright 2016 Redgate Software. Privacy Policy