Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
No, not possible. Easiest way to do this would be to write a perl or python script that iterates through a query based on systable/syscolumn. Pulling an entire database through a script is expensive, but I'm sure you know that already. –  Phil Mar 9 '13 at 1:00
    
These question mention some tools and some t-sql solutions stackoverflow.com/q/5818598/330315 and stackoverflow.com/q/8435963/330315 and stackoverflow.com/q/5350088/330315 –  a_horse_with_no_name Mar 9 '13 at 9:55

4 Answers 4

You can do a query like this:

Select o.name as table, c.name as column from syscolumns c, sysobjects o
Where c.id = o.id and
c.name like "stringImLookingFor"

The first name is the table name and the second name is the column name. You can use '%' as a wild card if you don't know the exact name you are looking for.

share|improve this answer

If it's practical to dump the database to disk as text, you can just use ordinary text utilities to search the dump. grep is your friend.

share|improve this answer
    
It's a Sybase ASE database, so a dump is a binary file, that can't be grepped. –  Michael Gardner Mar 11 '13 at 16:36

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)
   + "go"
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
share|improve this answer

I have created a script for this, where I printed all TableNames, ColumnNames containing the search string.

Not performance optimised!!! using a cursor to loop through the relevant columns, so it might not make sense to run this on any DB (depending on size, number of tables/cols etc.)

However, for a one-time search it's a nice utility to find a string in a DB.

-----------------------------------------------------------------------------------------------------
-- SYBASE - SCRIPT TO FIND STRING IN ANY COLUMN IN TABLE AND PRINT TableName/ColumnName TO RESULTS --
-----------------------------------------------------------------------------------------------------

-- tested on Sybase ASE 15.7

set nocount off

-- CREATE OBJECTS REQUIRED FOR SCRIPT
create table #SearchString (SearchString varchar(100))
go

-- SET SEARCH STRING
declare @search_string  varchar(100)
set @search_string = 'SEARCH_STRING'

-- WRITE SEARCH STRING TO TEMP TABLE TO STORE IT AWAY AND BE ABLE TO READ IT IN NEXT BATCH
insert into #SearchString (SearchString)
    values (@search_string)

-- GET ALL RELEVANT TABLES AND COLUMNS
insert #TabCol
    select object_name(o.id) as TableName, c.name as ColumnName
        from sysobjects o, syscolumns c 
    where o.type = 'U' -- ONLY USER TABLES
          and c.usertype in (1,2,18,19,24,25,42) -- ONLY LOOK FOR CHAR, VARCHAR, ETC.
          and c.id = o.id
          and c.name is not null
          and c.length >= datalength(@search_string)
go

-- GET TOTAL NUMBER OF RELEVANT COLUMNS
select count(*) as ColumnsToLoopThrough from #TabCol
go

-- CREATE CURSOR TO LOOP THROUGH TABLES AND COLUMNS TO FIND COLUMNS CONTAINING THE SEARCH STRING
declare cur cursor for 
select TableName, ColumnName from #TabCol order by TableName, ColumnName
for read only
go

-- VARIABLE DEFINITION
declare
    @table_name     SYSNAME,
    @table_id       int,
    @column_name    SYSNAME,
    @sql_string     varchar(2000),
    @search_string  varchar(100)

-- GET SEARCH STRING FROM TABLE
select @search_string = SearchString from #SearchString

-- CURSOR INIT
open cur

fetch cur into @table_name, @column_name

-- LOOP THROUGH TABLES AND COLUMNS SEARCHING FOR SEARCH STRING AND PRINT IF FOUND
while (@@sqlstatus != 2)
begin
    set @sql_string = 'if exists (select * from ' + @table_name + ' where [' + @column_name + '] like ''%' + @search_string + '%'') print ''' + @table_name + ', ' + @column_name + ''''
    execute(@sql_string)
    fetch cur into @table_name, @column_name
end
go

-- CLEAN-UP
close cur
deallocate cur

drop table #SearchString
drop table #TabCol
go

Cheers

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.