asked
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
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.
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 As Håkan has said, this isn't going to run nicely, but as it stands, it isn't going to run at all!
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/
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.
