I've tried to make my own Version Control System inside a database for all db objects and used the following function and view. It's quite old (ASA 😎 and not complete (indexes, foreign keys, ...) but it's a start.
create FUNCTION "DBA"."VCS_TableDef"(in tableid integer,in Creator varchar(255),in Name varchar(255),in tbconstr long varchar)
returns long varchar
begin
declare res long varchar;
declare pkey integer;
set res='CREATE TABLE "'+Creator+'"."'+Name+'"'+"char"(13)+"char"(10)+'('+"char"(13)+"char"(10);
set pkey=0;
for curs as curs1 scroll cursor for
select "char"(9)+'"'+column_name+'"'+"char"(9)+isnull((select type_name from sys.sysusertype where type_id = user_type),(select domain_name from sys.sysdomain where domain_id = syscolumn.domain_id))
+(if user_type is null then(if domain_id in( 11,7,8,9) then '('+string(width)+')' else(if domain_id = 3 then '('+string(width)+','+string(scale)+')'
endif)
endif) endif)+(if nulls = 'N' then ' NOT NULL'
else ' NULL'
endif)+(if "default" is not null then ' DEFAULT '+"default"+(if "check" is not null then ' check '+"check"
endif)
endif) as coldef,
(select check_defn from sys.syscheck where check_id = (select constraint_id from sys.sysconstraint where table_object_id = tableid and ref_object_id = syscolumn.object_id and constraint_type = 'C')) as "check" from sys.syscolumn where table_id = tableid order by column_id asc do
if pkey <> 0 then
set res=res+','+"char"(13)+"char"(10)
end if;
set res=res+coldef;
set pkey=1 end for;
set pkey=0;
for curs as curs2 scroll cursor for
select '"'+column_name+'"' as colnm from sys.syscolumn where table_id = tableid and pkey = 'Y' order by column_id asc do
if pkey = 0 then
set res=res+','+"char"(13)+"char"(10)+"char"(9)+'PRIMARY KEY (';
set pkey=1
else
set res=res+', '
end if;
set res=res+colnm end for;
if pkey <> 0 then
set res=res+')'
end if;
set pkey=0;
for curs as curs3 scroll cursor for
select '"'+column_name+'"' as colnm from sys.sysindex,sys.sysixcol,sys.syscolumn where sysindex.table_id = tableid and sysindex."unique" = 'U' and sysindex.index_id = sysixcol.index_id and sysixcol.table_id = tableid and syscolumn.table_id = tableid and sysixcol.column_id = syscolumn.column_id order by sysindex.index_id asc do
if pkey = 0 then
set res=res+','+"char"(13)+"char"(10)+"char"(9)+'UNIQUE (';
set pkey=1
else
set res=res+', '
end if;
set res=res+colnm end for;
if pkey <> 0 then
set res=res+')'
end if;
if tbconstr is not null then
set res=res+','+"char"(13)+"char"(10)+"char"(9)+tbconstr
end if;
set res=res+"char"(13)+"char"(10)+')';
return(res)
end;
CREATE VIEW "DBA"."VCS_SourceCode" as
select sysusers.name as srccreator,
sysprocedure.proc_name as srcname,
proc_defn as srcsource,'P' as srcart,
proc_id as srcid from
sys.sysprocedure,dbo.sysusers where
creator = uid union all
select sysusers.name as srccreator,
systable.table_name as srcname,
(if table_type = 'View' then view_def else DBA.VCS_TableDef(table_id,srccreator,srcname,view_def) endif) as srcsource,
substr(table_type,1,1) as srcart,
table_id as srcid from
sys.systable,dbo.sysusers where
creator = uid union all
select(select user_name from SYS.SYSUSERPERM where
user_id = (select SYSTABLE.creator from SYS.systable where systable.table_id = systrigger.table_id)),
trigger_name as srcname,
trigger_defn as srcsource,'T' as srcart,
trigger_id as srcid from
sys.systrigger where
foreign_table_id is null;
I also used it to search for specific code segments, before this functionality was available in Sybase Central.
select * from vcs_sourcecode where srcsource like '%inout%'
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.