Solved

Add ntext to existing Search stored procedure

Posted on 2006-11-01
7
472 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
0
Comment
Question by:falahati_sina
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 17853326
Hi,

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

Regards
  David
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17853348
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
0
 

Author Comment

by:falahati_sina
ID: 17857707
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
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 11

Expert Comment

by:rw3admin
ID: 17859067
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

0
 

Author Comment

by:falahati_sina
ID: 17861518
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
0
 
LVL 11

Accepted Solution

by:
rw3admin earned 500 total points
ID: 17861598
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
0
 

Author Comment

by:falahati_sina
ID: 17861696
Thanks :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql case statement to calculate totals 5 37
SQL Syntax 6 41
access to sql migration 5 24
SQL question - help with insert for missing info 5 21
I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question