By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Regex in Stored Procedure Problem

100+
P: 296
Whe I execute the below procedure by EXEC SearchAllTables '[0-9]{3}' no results are coming -->


Expand|Select|Wrap|Line Numbers
  1. drop proc SearchAllTables
  2. GO
  3.  
  4.  
  5. CREATE PROC SearchAllTables
  6. (
  7.     @SearchStr nvarchar(100)
  8. )
  9. AS
  10. BEGIN
  11.  
  12.     CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  13.  
  14.     SET NOCOUNT ON
  15.  
  16.     DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
  17.     SET  @TableName = ''
  18.     SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  19.  
  20.     WHILE @TableName IS NOT NULL
  21.     BEGIN
  22.         SET @ColumnName = ''
  23.         SET @TableName = 
  24.         (
  25.             SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  26.             FROM     INFORMATION_SCHEMA.TABLES
  27.             WHERE         TABLE_TYPE = 'BASE TABLE'
  28.                 AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  29.                 AND    OBJECTPROPERTY(
  30.                         OBJECT_ID(
  31.                             QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  32.                              ), 'IsMSShipped'
  33.                                ) = 0
  34.         )
  35.  
  36.         WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  37.         BEGIN
  38.             SET @ColumnName =
  39.             (
  40.                 SELECT MIN(QUOTENAME(COLUMN_NAME))
  41.                 FROM     INFORMATION_SCHEMA.COLUMNS
  42.                 WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
  43.                     AND    TABLE_NAME    = PARSENAME(@TableName, 1)
  44.                     AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
  45.                     AND    QUOTENAME(COLUMN_NAME) > @ColumnName
  46.             )
  47.  
  48.             IF @ColumnName IS NOT NULL
  49.             BEGIN
  50.                 INSERT INTO #Results
  51.                 EXEC
  52.                 (
  53.                     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
  54.                     FROM ' + @TableName + ' (NOLOCK) ' +
  55.                     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  56.                 )
  57.             END
  58.         END    
  59.     END
  60.  
  61.     SELECT ColumnName, ColumnValue FROM #Results
  62. END
May 3 '08 #1
Share this Question
Share on Google+
1 Reply


debasisdas
Expert 5K+
P: 8,127
do you have matching data in the table for the query to filter ?
May 5 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.