[dba-SQLServer] search entire db

I used the code from the listing and had no typos
If you have already created the sproc, change the word CREATE to ALTER

--****************************** start of sproc

--This stored procedure creates a temporary table for storing search
result data and steps through each column found.

CREATE PROC [dbo].[SearchAllTablesAllColumns]
(
   @SearchStr nvarchar(100)
)
AS
BEGIN
   -- Purpose: To search all columns in all tables for a given search string
   -- Written by: Francisco Tapia
   -- Site: http://sqlthis.blogspot.com
   -- Inspired by: Narayana Vyas Kondreddi
   -- http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
   -- Tested on: SQL Server 7.0, 2000 and 2005
   -- Date modified: June 23, 2009

   CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

   SET NOCOUNT ON

   DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT

   SELECT @TableName = '', @ColumnName = ''
   SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')

      WHILE (@ColumnName IS NOT NULL)
      BEGIN
      SET @ColumnName = (
         SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)
         + '|' + QUOTENAME(C.Column_name))
               FROM INFORMATION_SCHEMA.COLUMNS c
                  INNER JOIN INFORMATION_SCHEMA.TABLES t on
c.table_schema = t.table_schema and c.Table_name = t.table_name
               WHERE    T.TABLE_TYPE = 'BASE TABLE'
                  AND C.DATA_TYPE IN ('char', 'varchar', 'nchar',
'nvarchar', 'text', 'ntext')
                  AND   QUOTENAME(C.Table_Schema) + '.' +
QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName

+ '.' + @ColumnName

               )

         SET @Parse = PATINDEX ('%|%', @ColumnName)

         SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)
         SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1,
LEN(@ColumnName))

         IF @ColumnName IS NOT NULL
         BEGIN
            INSERT INTO #Results
            EXEC
            (
               'SELECT ''' + @TableName + '.' + @ColumnName + ''', SUBSTRING('
               + @ColumnName + ',1, 3630)
               FROM ' + @TableName + ' (NOLOCK) ' +
               ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr
            )
         END
      END

   SELECT ColumnName, ColumnValue FROM #Results
   ORDER BY ColumnName

END

--******************************** END of Sproc

This is how you call the procedure:

EXEC SearchAllTablesAllColumns 'YourSearchTextHere'




On Tue, May 25, 2010 at 9:43 AM, Jean-Paul natola <jnatola at hotmail.com> wrote:
>
> yes, and i cought the typo
> 'SearcAllTablesAllColumns'.
>
>
>
> I'm not entirely sure how it works in sql but , maybe im saving it incorrectly? or in the wrong location?
>
>
>
>
>
>
>
>
>> Date: Tue, 25 May 2010 09:36:14 -0700
>> From: dbdoug at gmail.com
>> To: dba-sqlserver at databaseadvisors.com
>> Subject: Re: [dba-SQLServer] search entire db
>>
>> I'm not trying to be rude here, but is the name of the sproc that you saved
>> exactly the same as the name you are trying to execute?
>>
>> Doug Steele
>>
>> On Tue, May 25, 2010 at 9:23 AM, Jean-Paul natola <jnatola at hotmail.com>wrote:
>>
>> >
>> > I know this is trivial to the rest of you, but HOW do i add/register/create
>> > it
>> >
>> > I tried to create new sproc , copied the code into it , saved it and when I
>> > try to execut it it returns
>> >
>> >
>> >
>> > Msg 2812, Level 16, State 62, Line 1
>> >
>> > Could not find stored procedure 'SearcAllTablesAllColumns'.
>> >
>> >
>> >
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
>
> _________________________________________________________________
> The New Busy is not the old busy. Search, chat and e-mail from your inbox.
> http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_3
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>