search entire Sybase database for a string of data

Question

I have a Sybase database and SQL Anywhere 16 for querying data. I need to find some example pieces of data in the database. There are hundreds of tables with unhelpful names.

I'm not a Sybase expert, although I do know the SQL language (I'm a SQL Server developer).

Is there a script that loops through all of the columns of all tables looking for this sample data?

thanks,

Conrad

Answer 1

You can try the following:

CREATE TABLE "DBA"."table_track" (
"Table_name" VARCHAR(200) NULL,
"Column_name" VARCHAR(200) NULL,
"ls_search" VARCHAR(2000) NULL
) IN "system";

CREATE OR REPLACE PROCEDURE "DBA"."sp_get_data"(as_search   varchar(2000))
BEGIN
    DECLARE cur_test NO SCROLL cursor for select table_Name, column_name from Table_List ;

    DECLARE ls_sql varchar(20000);
    DECLARE ls_table_name varchar(80);
    DECLARE ls_column_name varchar(80);
    DECLARE ld_count Numeric(10,0);  
    DECLARE le_total_column_count   INTEGER;
    DECLARE le_counter  INTEGER;

    If Exists (SELECT 1 FROM sysobjects WHERE name ='Table_List' AND Type='U') Then
        Drop Table Table_List;
     End IF;

    SELECT
        sys.SYSTABLE.table_name,
        sys.SYSTABCOL.column_name   
    Into Table_List
    FROM
        sys.SYSTABLE
    Inner Join
         sys.SYSTABCOL on sys.SYSTABLE.table_id = sys.SYSTABCOL.table_id
    Where
        sys.SYSTABLE.table_type = 'BASE'
        AND sys.SYSTABLE.creator = 1
        and count > 0
        and width >= 4
        AND sys.systable.table_name not in ('table_track')
        AND (sys.SYSTABCOL.base_type_str like '%char%' OR sys.SYSTABCOL.base_type_str like '%xml%');

    select count() into le_total_column_count from table_list;

    Message as_search to client;

    set le_counter = 0;

    Open cur_test;
    lp: loop
     Fetch Next cur_test into ls_table_name, ls_column_name; 
        If SQLCODE <> 0 Then
            LEAVE lp
        End If;

        SET le_counter = le_counter + 1;

        message 'Search column ' + string(le_counter) + ' of ' + string(le_total_column_count) TO CLIENT;

        Set ls_sql = 'select count(*) into ld_count from "' + ls_table_name + '" where "' + ls_column_name + '" like ''%'+ as_search + '%''';      
        EXECUTE (ls_sql);
        If SQLCODE <> 0 Then
            return -1;
        End If;

        If ld_count <> '' and ld_count is not null  Then
            If ISNULL(ld_count,0) > 0 Then
                Insert into table_track (table_name,column_name,ls_search) values (ls_table_name,ls_column_name,as_search);
            END If;
        END If ;

    End Loop;
    Close cur_test;
    Deallocate cur_test;
    commit;
    // Select * from table_track;
END; 

call sp_get_data('test');

select * from table_track;

I think you can get the idea from this and then you can make changes according to your requirement.