[dba-SQLServer] search entire db-SOLVED

Jean-Paul natola jnatola at hotmail.com
Tue May 25 14:11:34 CDT 2010 

By the way,  thanks  for posting a clean version,

 

I would have spent hours (well more hours than i did)  trying to figure out what i was doing wrong.

 

and of course thanks to Francisco for actually writing it

 



 
> Date: Tue, 25 May 2010 11:58:16 -0700
> From: davidmcafee at gmail.com
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] search entire db-SOLVED
> 
> 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
> 
 		 	   		  
_________________________________________________________________
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

More information about the dba-SQLServer mailing list