Today, I am gonna show how to search any text in entire database in Sybase.
sybase, sybase, sybaseCREATE PROCEDURE "DBA"."sp_get_data"(as_search
BEGIN
DECLARE cur_test 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);
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 <> 0
AND (sys.SYSTABCOL.base_type_str like '%char%' OR sys.SYSTABCOL.base_type_str like '%binary%' OR sys.SYSTABCOL.base_type_str like '%bit%' OR sys.SYSTABCOL.base_type_str like '%xml%');
If Exists (SELECT 1 FROM sysobjects WHERE name ='table_track' AND Type='U') Then
Drop Table table_track;
End IF;
CREATE TABLE table_track
(
Table_name varchar(200),
Column_name varchar(200)
);
If Exists (SELECT 1 FROM sysobjects WHERE name ='sql_execution_script' AND Type='U') Then
Drop Table sql_execution_script;
End IF;
CREATE TABLE sql_execution_script
(
Sql_syntax varchar(200)
);
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 ls_sql = 'select count(*) into ld_count from ' + ls_table_name + ' where "' + ls_column_name + '" like ''%'+ as_search + '%''';
INSERT INTO sql_execution_script VALUES (ls_sql);
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 values (ls_table_name,ls_column_name)
END If;
END If ;
End Loop;
Close cur_test;
Deallocate cur_test;
Select * from table_track;
END;
------------------------------------------
For finding executed SQL Script:
Select * from sql_execution_script;
