We help IT Professionals succeed at work.

Add ntext to existing Search stored procedure

falahati_sina
on
Medium Priority
512 Views
Last Modified: 2011-10-03
We have to a stored procedure, which can perform searches inside nchar, nvarchar, ... except ntext.
It uses "LIKE" for string matching.

I want to extend it, so it supports ntext... I know that LIKE is not supported for ntext, but PATINDEX is... How do I have to change my script to include ntext fields? (The requirement is not to return the whole content of ntext field as result, it can be truncated...)

Here is the script:


---------------------------------------------------------------------------

ALTER PROC [dbo].[SearchAllTables]
(
  @SearchStr nvarchar(100)
)
AS
BEGIN

  -- See http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
  -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
  -- Purpose: To search all columns of all tables for a given search string
  -- Written by: Narayana Vyas Kondreddi
  -- Site: http://vyaskn.tripod.com
  -- Tested on: SQL Server 7.0 and SQL Server 2000
  -- Date modified: 28th July 2002 22:50 GMT

  -- Modifications by S. Falahati (26.10.2006)
  --   Replace tabs by '   ' for better formatting in SQL Server Management tool  
  --   Exclude aspnet tables (using NOT LIKE statement)

  -- pasha701 (28.10.2006)
  -- add PK settings            


  CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3000),PKName nvarchar(80),PKValue nvarchar(550))

  SET NOCOUNT ON

  DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110),
      @PKColumnName nvarchar(80), @PKValueString nvarchar(550)
  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
    )
      --*- generate PK options -*--
      Set  @PKValueString=''
      Set  @PKColumnName=''

      select @PKColumnName=@PKColumnName+Left(RTrim(kcu.COLUMN_NAME),80)+';',
            @PKValueString=@PKValueString+'Convert(nvarchar(4000),'+RTrim(kcu.COLUMN_NAME)+')+'';''+'
      --kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
       from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
              join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
                on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
               and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
               and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
               and kcu.TABLE_NAME = tc.TABLE_NAME
             where       
                    tc.TABLE_SCHEMA  = PARSENAME(@TableName, 2)
                      AND  tc.TABLE_NAME  = PARSENAME(@TableName, 1) and
            tc.CONSTRAINT_TYPE = 'PRIMARY KEY';
      --
      if Len(@PKValueString)>0
            Set @PKValueString=SubString(@PKValueString,0,Len(@PKValueString)-4)
      else
            Set @PKValueString=''''''

      if Len(@PKColumnName)>0
            Set @PKColumnName=SubString(@PKColumnName,1,Len(@PKColumnName)-1)
      --/
    WHILE (@TableName IS NOT NULL) AND (@TableName NOT LIKE '%aspnet%')
           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')
          AND  QUOTENAME(COLUMN_NAME) > @ColumnName
      )
 
      IF @ColumnName IS NOT NULL
      BEGIN
        INSERT INTO #Results
        EXEC
        (
          'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3000) ,'''+@PKColumnName+''',Left('+@PKValueString+',550)
           FROM ' + @TableName + ' (NOLOCK) ' +
          ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
        )
      END
    END  
  END

  SELECT ColumnName, ColumnValue ,
      PKName,PKValue
  FROM #Results
END

---------------------------------------------------------------------------


Thanks,

Sina Falahati
Comment
Watch Question

David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi,

Any chance for doing this on SQL 2005 which has way better support for text and ntext, including using like.

Regards
  David

Commented:
Patindex returns you the starting position of your search string, you can use

where PATINDEX(%pattern%,expression)>0 -- for Like
where PATINDEX(%pattern%,expression)=0 -- for Not Like

Author

Commented:
Where / How would I have to implement this? And how can I truncate the ntext if I want to return part of it (whole could get tool large?)....
Thanx for comments.
Sina

Commented:
Sina
I am sorry if I confused you, I was saying wherever you are using like in your statement replace it with appropriate PATINDEX statement, replace all the LIKE with PATINDEX(%pattern%,expression)>0  for example from your code
Change

>> FROM ' + @TableName + ' (NOLOCK) ' +
          ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2<<
to
FROM ' + @TableName + ' (NOLOCK) ' +
          ' WHERE PATINDEX(' +@SearchStr2+','+@ColumnName+')>0'

and replace all NOT LIKE as

>>WHILE (@TableName IS NOT NULL) AND (@TableName NOT LIKE '%aspnet%') <<
to
WHILE (@TableName IS NOT NULL) AND (PATINDEX('%aspnet%',@TableName)=0)

this will work for all , ntext, text, char, nchar, varchar and nvarchar

rw3admin

Author

Commented:
Thank you rwadmin,

Now, it almost works fine except I get the following error:

Running [dbo].[SearchSpecificTables] ( @SearchStr = es ).

Argument data type ntext is invalid for argument 1 of left function.
Argument data type ntext is invalid for argument 1 of left function.
Argument data type ntext is invalid for argument 1 of left function.

Gues ntext is not supported in LEFT???

Sina
Commented:
HEY
that should be another thread :).... just kidding
I see the biggest LEFT string you are getting is 3000 characters,
>>LEFT(' + @ColumnName + ', 3000) <<
if you can convert your @ColumnName to varchar(8000) (ONLY IN LEFT STRINGS......) this should work for you

so change
>>LEFT(' + @ColumnName + ', 3000) <<
to
LEFT(cast('+ @ColumnName+' as Varchar(8000)),3000) .....

rw3admin

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.