-- SearchAllTables.sql -- 26 Jul 08, www.troubleshootingninja.com -- -- This Sybase ASE proc was the winner of a small challenge -- I issued at Ars Technica's forums Programmer's Symposium. -- http://episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/846003143931 -- It was written by KwikSilvr and is used to search all tables within -- a database for a string pattern. -- -- It searches only user tables but can be changed to search system tables -- by changing type = 'U" to type = "S" or adding an OR for it. -- Once created you can use it simply as: -- 1> exec SearchAllTables "myString" -- 2> go create proc SearchAllTables(@SearchStr nvarchar(100)) as begin create table #TableCount (TableName sysname, ColumnName sysname, ExpressionCount int) declare @table_name sysname declare @table_id int declare @column_name sysname declare @mySQL varchar(255) declare table_cursor cursor for select name, id from sysobjects where type = "U" for read only open table_cursor fetch table_cursor into @table_name, @table_id while (@@sqlstatus = 0) begin declare column_cursor cursor for select name from syscolumns where id=@table_id and usertype in (select usertype from systypes where type = (select type from systypes where name="varchar")) for read only open column_cursor fetch column_cursor into @column_name while (@@sqlstatus = 0) begin select @mySQL = 'insert #TableCount select "' + @table_name + '","' + @column_name + '",count(*) from ' + @table_name + ' where ' + @column_name + ' like "%' + @SearchStr + '%"' exec(@mySQL) fetch column_cursor into @column_name end close column_cursor deallocate cursor column_cursor fetch table_cursor into @table_name, @table_id end close table_cursor deallocate cursor table_cursor select * from #TableCount where ExpressionCount > 0 commit end