[dba-SQLServer] search entire db

back... Jean-Paul I noticed you're having some difficulty, yes the top of
the script uses this "USE" database name syntax replace the bracket text
with the name of your database, this creates the sproc for your use in the
said database


-Francisco
http://sqlthis.blogspot.com | Tsql and More...


On Tue, May 25, 2010 at 10:15 AM, Jean-Paul natola <jnatola at hotmail.com>wrote:

>
> on eother thing I noticed while looking at the existing sprocs in the
> account DB is that they start with
>
>
>
> USE [dbname]
>
>
>
> is that required as well?
>
>
>
>
>
>
> > 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 is redefining busy with tools for the New Busy. Get more from your
> inbox.
>
> http://www.windowslive.com/campaign/thenewbusy?ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_2
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>