[dba-SQLServer] search entire db

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