Reverse Engineer Sybase IQ dbspaces Without an ER Tool! | Tips & Tricks

For Sybase IQ it is sometimes handy to reverse engineer the statements that were used to create the various IQ dbspaces. You can do this with Power Designer but not everybody has access to the tool. Below you will find a script that does a basic attempt to reverse engineer the statements for the “create database” and “create dbspace” commands. The script is not complete with regard to all the possible options for these statements. Feel free to make adjustments or mail the author when you miss something. NB: Written specifically for IQ 15 ! begin declare @db_file long varchar; declare @iq_file long varchar; declare @msg_file long varchar; declare @temp_file long varchar; declare @block_size unsigned int; declare @chunk_size unsigned int; declare @reserve_size rowid; declare @user_dbspace long varchar; declare @user_dbfile_name long varchar; declare @user_file_name long varchar; declare @counter int; declare local temporary table #statement( seq int not null default autoincrement, statement_txt long varchar null); declare end_of_cursor exception for SQLSTATE ‘02000′; declare user_dbspaces cursor for select dbspace_name from sysdbspace where store_type = 2 and dbspace_name not in (‘IQ_SYSTEM_MAIN’, ‘IQ_SYSTEM_TEMP’, ‘IQ_SYSTEM_MSG’); declare user_dbfiles cursor for select f.dbfile_name, f.file_name from sysdbfile f, sysdbspace d where d.store_type = 2 and d.dbspace_name = @user_dbspace and d.dbspace_id = (more…)