Solved

Add ntext to existing Search stored procedure

Posted on 2006-11-01
7
459 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

706 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

17 Experts available now in Live!

Get 1:1 Help Now