Find all objects with the reference to the tbl

[alex]

I have ASA 8. How can I find all objects (procs, etc) with
the reference to the certain table name. I just want to be
sure that the table is not used anywhere and can be
dropped/backed up.
thanks a lot..

[Unknown]

Something like this may be the way to go

create variable my_table varchar(255);
set my_table = '%'||@table_name||'%';
--set my_table = '%'||'from'||'%';
select table_id object_id, table_name object_name, 'View' object_type
from sys.systable where table_type='VIEW' and view_def like my_table
union all
select proc_id,proc_name,'Proc' from sys.sysprocedure where
trim(proc_defn) like 'create function %' and proc_defn like my_table
union all
select proc_id,proc_name,'Func' from sys.sysprocedure where
trim(proc_defn) like 'create procedure %' and proc_defn like my_table union
all
select trigger_id, trigger_name, 'Trig' from sys.systrigger where
trigger_defn like my_table union all
select event_id, event_name, 'Event' from sys.sysevent where source like
my_table;

drop variable my_table ;

If you think you have user defined datatypes and/or check contstraints
that may also reference your tables, you will want to add UNION ALLs
to sys.sysusertype and sys.syscolumn ("check" column for SQL definition for
either datatype check constraint or column check constraint).

If you thing you have defaults that reference that table you may also need
to add UNION ALLs to sys.sysusertype and sys.syscolumn ("default" column
for the SQL definition).

Note: columns check and default use keywords so those, at least, will need
to be delimited.

Also note: in a case sensitive database you may also need to add Upper or
Lower
conversions to match table names that may be used in a different case than
your
search string (schema objects can be used case-insensitvely even in a
case-sensitive
database but your like clause will be case-sensitve in a case-sensitive
database.)

Or something like that

<alex> wrote in message news:462cf9ca.2ec...@sybase.com...

[Stephen Rice]

using grep on a schema only unload is probably the most efficient. V10
Sybase Central has a find facility that will look in SQL as well. I haven't
tried it against a v8 database but it works against a v9 one so I expect it
will work fine
--
/steve
Stephen Rice
Sybase iAnywhere

<alex> wrote in message news:462cf9ca.2ec...@sybase.com...

[Unknown]

Yeah

That works too! ;-)

(I actually do that more often myself but did the
SQL programmer thing since I was in that mode ....)

On UNix:
dbunload -n -r my_file_to_grep.txt -c ....
grep -i <your_table_name>
or on Windose:
dbunload -n -r my_file_to_grep.txt -c ....
find /I <your_table_name>

"Stephen Rice" <sriceATianywhereDOTcom> wrote in message
news:462d087b$1@forums-1-dub...