Jean-Paul natola
jnatola at hotmail.com
Tue May 25 13:21:44 CDT 2010
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