Solved

Add ntext to existing Search stored procedure

Posted on 2006-11-01
7
462 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with Sorting Full Text results 2 15
SQL Server stored proc 2 13
Help Extract Specific in SQL 8 27
SQL Server merge records in one table 2 14
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now