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

jhowe gravatar image

jhowe
1.1k 52 57 61

(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

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

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

Kev Riley gravatar image

Kev Riley ♦♦
53.1k 47 49 76

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1842
x987

asked: Oct 17, 2011 at 04:35 AM

Seen: 889 times

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