Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Add ntext to existing Search stored procedure

Posted on 2006-11-01
7
Medium Priority
?
492 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

916 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