Search every column in every table in Sybase Database

sybase

I'm been taxed with the task of creating an application that pulls data from our Micros Point of Sales system. The POS is using a Sybase database running on one of our servers. The database schema is very convoluted. I've been able to figure out most of the schema to gain access to the data I need, however, there are a few things still left to find. I know what they are called in the actual POS, but I can't seem to find them anywhere in the database (although I haven't actually looked through all 200+ tables).

I'm wondering if there's any way to run a query to search for a specific string in all the columns in all the tables. BTW, I'm using the "Interactive SQL" application that comes with the Sybase Database Client software to connect to the database.

Best Answer

I was being tasked to do similar task recently. IT is not easy in especially SybaseASE.

Option 1 :

You can write custom tool to BCP out data in files and then grep those files to search for particular text that you want to look for. You can use PERL regex to do as well or use native Unix/Linux commands to parse those files and find the required text that you want to look for.

Option 2 :

Another option (which is not that great - quick and dirty way using TSQL ) that worked for me .

Note: This will take long time (slow and unoptimized) depending on your database size, hardware, workload running on the server as well as database layout.

TEST, TEST and TEST it before hand !!

--- run below script and copy the output and run it in another query window.
set nocount on
declare @searchvalue varchar(255)
select @searchvalue = 'test string to search'---- replace this .. !!
print 'set nocount on'
print 'go'
print 'create table #results (table_name sysname, column_name sysname)'
print 'go"

select
   'insert #results select distinct '''
   + object_name(c.id)
   + ''' as table_name, '''
   + c.name
   + ''' as column_name from '
   + object_name(c.id)
   + ' where '
   + c.name
   + ' LIKE ''%'
   + @searchvalue
   + '%'''
   + char(10)
from
   syscolumns c, sysobjects o
where
   c.usertype in (
-- only look for char, varchar, text etc datatypes as we are not interested in int, datetime etc....
    1
    ,2
    ,18
    ,19
    ,24
    ,25
    ,42
    )
and o.type ='U' -- only user tables we are interested in
and c.id = o.id
and c.length>=datalength(@searchvalue )
--and object_name(c.id) = 'some table if you want to filter'

print 'select * from #results'
print 'go"
go
Related Question