David McAfee
davidmcafee at gmail.com
Tue May 25 13:58:16 CDT 2010
Yes, the copy I downloaded was like that too.
Francisco or Susan might want to look into that.
The other two scripts in the zip file (2000_findColumns.sql &
2005_findColumns) were both OK.
David
On Tue, May 25, 2010 at 11:21 AM, Jean-Paul natola <jnatola at hotmail.com> wrote:
>
>
> Evidently , due to browser/file format or whatever , mine wasnt as clean as your, I had all this on top of the filed
>
> {\rtf1\ansi\ansicpg1252\cocoartf949\cocoasubrtf460
>
> {\fonttbl\f0\fswiss\fcharset0 Helvetica;}
>
> {\colortbl;\red255\green255\blue255;}
>
> \margl1440\margr1440\vieww19960\viewh12760\viewkind0
>
> \pard\tx720\tx1440\tx2160\tx2880\tx3600\tx4320\tx5040\tx5760\tx6480\tx7200\tx7920\tx8640\ql\qnatural\pardirnatural
>
>
>
> GO\f0\fs24 \cf0 CREATE PROC [dbo].[SearchAllTablesAllColumns]\
>
>
>
>
>
>
>
>
>
>
>> Date: Tue, 25 May 2010 10:10:27 -0700
>> From: davidmcafee at gmail.com
>> To: dba-sqlserver at databaseadvisors.com
>> Subject: Re: [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
>> >
>> >
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
>
> _________________________________________________________________
> Hotmail has tools for the New 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_1
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>