Francisco Tapia
fhtapia at gmail.com
Tue May 25 14:09:53 CDT 2010
Yeah I'll get a hold of someone to correct it
On 5/25/10, David McAfee <davidmcafee at gmail.com> wrote:
> 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
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
--
Sent from my mobile device
-Francisco
http://sqlthis.blogspot.com | Tsql and More...