[Assassins]

Sybase ASE 12.5.1 (Server + Client + Docs + Instrument)


Год выпуска: 2004
Версия: 12.5.1/EBF 11665 ESD#2/P/NT (IX86)/OS 4.0/ase1251/1838/32-bit/OPT/Fri Feb 20 04:11:31 2004
Разработчик: Sybase
Платформа: Windows
Совместимость с Vista: Полная
Системные требования: СЕРВЕР, Проверена на системах:
Microsoft Windows 2000 Server/Advanced Server
Microsoft Windows 2003 Server Standard/Enterprise 32 bit
Microsoft Windows 2008 Server Enterprise Service Pack 1

Процессор:1x Intel Pentium IV
Оперативная память: 1 Гб
Место на жестком диске: Системный раздел: 0,5 Гб/Раздел данных минимум: 2 Гб

КЛИЕНТ:
Операционная система: Microsoft Windows 2000 Professional или Microsoft Windows XP SP1 32 Bit
Процессор 1x Intel Pentium III
Оперативная память 128 Мб
Место на жестком диске 500 Мб
Язык интерфейса: английский + русский
Таблэтка: Присутствует
Описание: Adaptive Server Enterprise (ASE) - мощная СУБД масштаба предприятия, являющаяся надежной платформой для критически-важных бизнес приложений. Sybase ASE традиционно используется в наиболее ответственных и требовательных к производительности системах, сохраняя при этом простоту в обслуживаниии и самую низкую среди "больших" СУБД совокупную стоимость владения (TCO).

Sybase Inc. представила Sybase Adaptive Server Enterprise 12.5.1 - сервер нового поколения, специально спроектированный для управления большими и сверхбольшим базами данных, в условиях постоянно увеличивающегося потока транзакций, при этом сохранив качество экономически эффективной платформы для управления данными.

Приложен инструмент: Embarkadero DBArtisan 7.0.0 + ключи
Рекомендуется для выполнения скриптов администрирования и написания хранимых процедур

Приложен инструмент: PowerBuilder 6.5.1 работает и на Висте. (при установке некоторое время висит, но потом все ставится)
Рекомендуется для написания Клиент Серверных приложений с родной базой (очень простой интерфейс в отличие от следующих версий)

Чтобы сервер Sybase ASE Server 12.5.1 был на любое количество конектов нужно:
копировать файл Disk1\#Crack\license.dat после создания инсталлятором файловой структуры, до создания экземпляра сервера...

/* Пример: создадим базу данных my_base размером 52 ГБ = (50 ГБ data + 2 ГБ Log) в BDArtisan */
/* для этого сделаем два девайса my_base1 и my_base2 по 25 Gb каждый, мах SIZE 32 Gb - 16 777 216 */

USE master
go
DISK INIT
NAME='my_base1',PHYSNAME='c:\sybase\data\my_base1.dat',
VDEVNO=2,
SIZE=12800000,
VSTART=0,
CNTRLTYPE=0
go
EXEC sp_diskdefault 'my_base1',defaultoff
go

USE master
go
DISK INITNAME='my_base2',
PHYSNAME='c:\sybase\data\my_base2.dat',
VDEVNO=3,
SIZE=12800000,
VSTART=0,
CNTRLTYPE=0
go
EXEC sp_diskdefault 'my_base2',defaultoff
go

/* создадим девайс my_base_log лог базы размером 2Gb */

USE master
go
DISK INITNAME='my_base_log',
PHYSNAME='c:\sybase\data\my_base_log.dat',
VDEVNO=4,
SIZE=1024000,
VSTART=0,
CNTRLTYPE=0
go
EXEC sp_diskdefault 'my_base_log',defaultoff
go

/* Итоговое создание базы my_base размером 52 Gb */

USE master
go
CREATE DATABASE my_baseON my_base1=25000, my_base2=25000
LOG ON my_base_log=2000
go
USE master

EXEC sp_dboption 'my_base','select into/bulkcopy/pllsort',true
go

EXEC sp_dboption 'my_base','trunc log on chkpt',true
go
USE my_base
go
CHECKPOINT
go

/* Далее мы увеличим размер базы tempdb до 4 ГБ */
/* создаем девайс 4 Gb */

USE master
go
DISK INITNAME='temp_db',
PHYSNAME='c:\sybase\data\temp_db.dat',
VDEVNO=5,
SIZE=2048000,
VSTART=0,
CNTRLTYPE=0
go
EXEC sp_diskdefault 'temp_db',defaultoff
go

/* увеличиваем размер базы tempdb */

USE master
go
ALTER DATABASE tempdbON temp_db=4000
go

/* Настройки производительности */
/* Почему нужно перенастраивать сервер? */

Так как Sybasе Enterprise Server 12.5.1 по умолчанию работает почти на любом железе. Поэтому, установки по умолчанию не будут использовать ресурсы компьютера полностью. Даже для средней персоналки, желательно перенастроить Sybasе сервер, как описано ниже. Примеры приведены для трех вариантов ОЗУ: 4Gb, 2Gb, и 512Kb.
Был взят сервер: Xeon(TM) CPU 2.80 ГГц, - два процессора ОЗУ 4.00 ГБ (чтобы эта память была доступна операционной системе, в файле c:\boot.ini нужно дописать в конце последней строки '/3Gb' .....WINDOWS="Windows Server 2003, Enterprise" /fastdetect /3Gb )
Операционная система сервера: Microsoft Windows Server 2003

/* Пример распределения памяти для ОЗУ 4.00 ГБ: */

EXEC sp_configure 'max memory', 1200000 -- доступная память для SQL сервера

/* Пример распределения памяти для ОЗУ 2.00 ГБ: */

EXEC sp_configure 'max memory', 750000 -- доступная память для SQL сервера

/* Далее для ОЗУ 4.00 ГБ и для ОЗУ 2.00 ГБ одинаково */

-- Следующую операцию можно делать только на серверах, где есть файл c:\boot.ini на всех остальных операционках ее нужно пропустить,
-- т.е на Vista Home premium операцию allocate max shared memory устанавливать в 1 не стоит, сервер после перезапуска не запустится....
-- Если сервер по какой то причине не стартонул, то вернуть предыдущую конфигурацию можно из файлов c:\sybase\server_name.??? переименовав последний в c:\sybase\server_name.cfg
EXEC sp_configure 'allocate max shared memory', 1 -- SQL сервер забирает всю память при старте

EXEC sp_configure "procedure cache size", 35000 -- размер процедурного кэша
EXEC sp_cacheconfig 'my_base cache','450.000M',mixed,strict -- выделяем кэш для базы my_base
EXEC sp_cacheconfig 'tempdb cache','450.000M',mixed,strict -- выделяем кэш для базы tempdb (для 4ГБ можно установить до 770.000M)
EXEC sp_configure 'user log cache size',16392 -- увеличиваем лог

/* Пример распределения памяти для ОЗУ 512 Mb: */

EXEC sp_configure 'max memory', 140000 -- доступная память для SQL сервера
EXEC sp_configure 'allocate max shared memory', 1 -- SQL сервер забирает всю память при старте
EXEC sp_configure "procedure cache size", 32000 -- размер процедурного кэша
EXEC sp_cacheconfig 'my_base cache','96.000M',mixed,strict -- выделяем кэш для базы my_base
EXEC sp_cacheconfig 'tempdb cache','50.000M',mixed,strict -- выделяем кэш для базы tempdb
EXEC sp_configure 'user log cache size',16392 -- увеличиваем лог

/* Руссифицируем сервер */

EXEC sp_configure 'default character set', 1, bin_iso_1
-- Обязательно установить в Редакторе рееста программа RegEdit
-- HKEY_LOCAL_MACHINE\SOFTWARE\SYBASE\Server\имя_бэкап_сервера_BS\Parameters\Arg5 ставим вместо -Jcp850 значение -Jiso_1 (Не потеряйте знак '-')
-- Сейчас перезапускаем Sybase SQL сервер через Службы (два раза т.к. с первого раза он не стартует)

/* После перезапуска распределяем память */

EXEC sp_bindcache 'my_base cache','my_base' -- указываем кэш базе My Base
EXEC sp_bindcache 'tempdb cache','tempdb' -- указываем кэш базе tempdb

/* Дополнительные настройки сервера */

EXEC sp_configure 'number of open index', 2000 -- число отктытых индексов
EXEC sp_configure 'number of open objects', 3000 -- чистло ткрытых объектов
EXEC sp_configure 'number of user connections', 150 -- число одновременных пользователей
EXEC sp_configure 'number of locks', 20000 -- число одновременных блокировок
EXEC sp_configure 'license information', 0 -- отключаем сообщение о лицензиях

/* Если сервер имеет несколько физических процессоров */

EXEC sp_configure 'number of engines at startup', 2 -- число физических процессоров
EXEC sp_configure 'max online engines', 2 -- число физических процессоров используемых SQL сервером.
-- Параметры 'number of engines at startup' и 'max online engines' связаны между собой и должны быть равны.

/* Этот скрипт нужен, чтобы из под PowerBuilder 6.5.1 были доступны таблицы на ASE 12.5.1 */
/* Скрипт нужно скомпилировать на базе sybsystemprocs например из под DBArtisan(а) */
IF OBJECT_ID('dbo.sp_pb60column') IS NOT NULL
BEGINDROP PROCEDURE dbo.sp_pb60column
IF OBJECT_ID('dbo.sp_pb60column') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60column >>>'
ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_pb60column >>>'
END
go
create proc sp_pb60column@id int
asdeclare @text varchar(255)
select @text = null
select c.colid, c.status, c.type, c.length, c.name, c.usertype, c.prec, c.scale, @text
from dbo.syscolumns c where c.id = @id and c.cdefault = 0
union
select c.colid, c.status, c.type, c.length, c.name, c.usertype, c.prec, c.scale, m.text
from dbo.syscolumns c, dbo.syscomments m where c.id = @id
and c.cdefault = m.id and m.colid = 1
order by c.colid
go
IF OBJECT_ID('dbo.sp_pb60column') IS NOT NULLPRINT '<<< CREATED PROCEDURE dbo.sp_pb60column >>>'
ELSEPRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60column >>>'
go
GRANT EXECUTE ON dbo.sp_pb60column TO public
go

IF OBJECT_ID('dbo.sp_pb60db') IS NOT NULL
BEGINDROP PROCEDURE dbo.sp_pb60db
IF OBJECT_ID('dbo.sp_pb60db') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60db >>>'
ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_pb60db >>>'

END
go
create proc sp_pb60db asselect name from master.dbo.sysdatabases
go
IF OBJECT_ID('dbo.sp_pb60db') IS NOT NULLPRINT '<<< CREATED PROCEDURE dbo.sp_pb60db >>>'
ELSEPRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60db >>>'
go
GRANT EXECUTE ON dbo.sp_pb60db TO public
go

IF OBJECT_ID('dbo.sp_pb60extcat') IS NOT NULL
BEGINDROP PROCEDURE dbo.sp_pb60extcat
IF OBJECT_ID('dbo.sp_pb60extcat') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60extcat >>>'
ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_pb60extcat >>>'[/spoiler]END
go
create procedure sp_pb60extcat
as
declare @tbl char(1)
declare @col char(1)
declare @fmt char(1)
declare @vld char(1)
declare @edt char(1)
declare @tblproc char(1)
declare @colproc char(1)
declare @fmtproc char(1)
declare @vldproc char(1)
declare @edtproc char(1)
declare @existbuff char(10)
if exists(select id from dbo.sysobjects where id = object_id('dbo.pbcattbl'))
select @tbl = 'Y'
elseselect @tbl = 'N'
if exists
(select id from dbo.sysobjects where id = object_id('dbo.pbcatcol'))
select @col = 'Y'
elseselect @col = 'N'
if exists
(select id from dbo.sysobjects where id = object_id('dbo.pbcatfmt'))
select @fmt = 'Y'
elseselect @fmt = 'N'
if exists(select id from dbo.sysobjects where id = object_id('dbo.pbcatvld'))
select @vld = 'Y'[/spoiler]elseselect @vld = 'N'
if exists(select id from dbo.sysobjects where id = object_id('dbo.pbcatedt'))
select @edt = 'Y'
else
select @edt = 'N'
if exists(select id from dbo.sysobjects where id = object_id('dbo.pb_cattbl'))
select @tblproc = 'Y'
elseselect @tblproc = 'N'
if exists(select id from dbo.sysobjects where id = object_id('dbo.pb_catcol'))
select @colproc = 'Y'[/spoiler]elseselect @colproc = 'N'
if exists
(select id from dbo.sysobjects where id = object_id('dbo.pb_catfmt'))
select @fmtproc = 'Y'
elseselect @fmtproc = 'N'
if exists(select id from dbo.sysobjects where id = object_id('dbo.pb_catvld'))
select @vldproc = 'Y'[/spoiler]elseselect @vldproc = 'N'
if exists(select id from dbo.sysobjects where id = object_id('dbo.pb_catedt'))
select @edtproc = 'Y'[/spoiler]elseselect @edtproc = 'N'
select @existbuff = @tbl + @col + @fmt + @vld + @edt +@tblproc + @colproc + @fmtproc +@vldproc + @edtproc
select @existbuff
go
IF OBJECT_ID('dbo.sp_pb60extcat') IS NOT NULLPRINT '<<< CREATED PROCEDURE dbo.sp_pb60extcat >>>'
ELSEPRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60extcat >>>'
go
GRANT EXECUTE ON dbo.sp_pb60extcat TO public
go

IF OBJECT_ID('dbo.sp_pb60fktable') IS NOT NULL
BEGINDROP PROCEDURE dbo.sp_pb60fktable
IF OBJECT_ID('dbo.sp_pb60fktable') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60fktable >>>'
ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_pb60fktable >>>'[/spoiler]END
go
create procedure sp_pb60fktable@objname varchar(61) = null
as
declare @objid int
declare @isolevel int /* ptrack 325579 isolation level */

if (@objname is null)return (1)
select @objid = object_id(@objname)
/* ptrack 325579 override isolation level 0 default */
select @isolevel = @@isolation
if @isolevel = 0
beginset transaction isolation level 1
end

select o.name, o.id, o.type, o.uid, user_name(o.uid)
from dbo.sysobjects o, dbo.sysreferences r
where r.reftabid = @objid and r.tableid = o.id

if @isolevel = 0
beginset transaction isolation level 0
end
go
IF OBJECT_ID('dbo.sp_pb60fktable') IS NOT NULLPRINT '<<< CREATED PROCEDURE dbo.sp_pb60fktable >>>'
ELSEPRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60fktable >>>'
go
GRANT EXECUTE ON dbo.sp_pb60fktable TO public
go

IF OBJECT_ID('dbo.sp_pb60foreignkey') IS NOT NULL
BEGINDROP PROCEDURE dbo.sp_pb60foreignkey
IF OBJECT_ID('dbo.sp_pb60foreignkey') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60foreignkey >>>'
ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_pb60foreignkey >>>'[/spoiler]END
go
create proc sp_pb60foreignkey
@objname varchar(92)
as
declare @objid int /* the object id of the fk table */
declare @keyname varchar(30) /* name of foreign key */
declare @constid int /* the constraint id in sysconstraints */
declare @keycnt smallint /* number of columns in pk */
declare @stat int
declare @isolevel int /* ptrack 325579 isolation level */

select @objid = object_id(@objname)
if (@objid is null)
begin
return (1)
end
select @stat = sysstat2
from dbo.sysobjects
where id = @objid and (sysstat2 & 2) = 2
if (@stat is null)
beginreturn (1)
end

/* Now I know this table has one or more foreign keys. */
/* ptrack 325579 override isolation level 0 default */
select @isolevel = @@isolation
if @isolevel = 0
beginset transaction isolation level 1
end

select o1.name, r.keycnt, o2.name, user_name(o2.uid),r.fokey1, r.fokey2, r.fokey3, r.fokey4, r.fokey5, r.fokey6,
r.fokey7, r.fokey8, r.fokey9, r.fokey10, r.fokey11, r.fokey12,
r.fokey13, r.fokey14, r.fokey15, r.fokey16
from dbo.sysconstraints c, dbo.sysobjects o1,dbo.sysreferences r, dbo.sysobjects o2
where c.tableid = @objid andc.status = 64 and
c.constrid = o1.id and
o1.type = 'RI' and
c.constrid = r.constrid and
r.reftabid = o2.id
if @isolevel = 0
beginset transaction isolation level 0
end
go
IF OBJECT_ID('dbo.sp_pb60foreignkey') IS NOT NULLPRINT '<<< CREATED PROCEDURE dbo.sp_pb60foreignkey >>>'
ELSEPRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60foreignkey >>>'
go
GRANT EXECUTE ON dbo.sp_pb60foreignkey TO public
go

IF OBJECT_ID('dbo.sp_pb60index') IS NOT NULL
BEGINDROP PROCEDURE dbo.sp_pb60index
IF OBJECT_ID('dbo.sp_pb60index') IS NOT NULLPRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60index >>>'
ELSEPRINT '<<< DROPPED PROCEDURE dbo.sp_pb60index >>>'[/spoiler]END
go
create procedure sp_pb60index
@objname varchar(92) /* the table to check for indexes */
as
declare @objid int /* the object id of the table */
declare @indid int /* the index id of an index */
declare @key1 varchar(30) /* first key */
declare @key2 varchar(30) /* second key */
declare @key3 varchar(30) /* third key */
declare @key4 varchar(30) /* fourth key */
declare @key5 varchar(30) /* ... */
declare @key6 varchar(30)
declare @key7 varchar(30)
declare @key8 varchar(30)
declare @key9 varchar(30) /* ... */
declare @key10 varchar(30)
declare @key11 varchar(30)
declare @key12 varchar(30)
declare @key13 varchar(30) /* ... */
declare @key14 varchar(30)
declare @key15 varchar(30)
declare @key16 varchar(30)

declare @unique smallint /* index is unique */
declare @clustered smallint /* index is clustered */
declare @isolevel int /* ptrack 325579 isolation level */
/*
** Check to see the the table exists and initialize @objid.
*/
select @objid = object_id(@objname)

/*
** Table doesn't exist so return.
*/
if @objid is null
beginreturn
end

/*
** See if the object has any indexes.
** Since there may be more than one entry in sysindexes for the object,
** this select will set @indid to the index id of the first index.
*/
select @indid = min(indid)from sysindexeswhere id = @objidand indid > 0
and indid < 255[/spoiler]
/*
** If no indexes, return.
*/
if @indid is null
beginreturn
end

/* ptrack 325579 override isolation level 0 default */
select @isolevel = @@isolation
if @isolevel = 0
beginset transaction isolation level 1
end

/*
** Now check out each index, figure out it's type and keys and
** save the info in a temporary table that we'll print out at the end.
*/
create table #spindtab
(index_name varchar(30),
index_num int,
index_key1 varchar(30) null,
index_key2 varchar(30) null,
index_key3 varchar(30) null,
index_key4 varchar(30) null,
index_key5 varchar(30) null,
index_key6 varchar(30) null,
index_key7 varchar(30) null,
index_key8 varchar(30) null,
index_key9 varchar(30) null,
index_key10 varchar(30) null,
index_key11 varchar(30) null,
index_key12 varchar(30) null,
index_key13 varchar(30) null,
index_key14 varchar(30) null,
index_key15 varchar(30) null,
index_key16 varchar(30) null,
index_unique smallint,
index_clustered smallint,
index_status smallint,
index_status2 int
)

while @indid != null
begin/*
** First we'll figure out what the keys are.
*/
declare @i int
declare @thiskey varchar(30)
declare @lastindid int

select @i = 1

set nocount on

while @i <= 16
beginselect @thiskey = index_col(@objname, @indid, @i)

if @thiskey = null
begingoto keysdone
end

if @i = 1
beginselect @key1 = index_col(@objname, @indid, @i)
end
else
if @i = 2
beginselect @key2 = index_col(@objname, @indid, @i)
end
else
if @i = 3
beginselect @key3 = index_col(@objname, @indid, @i)
end
else
if @i = 4
beginselect @key4 = index_col(@objname, @indid, @i)
end
else
if @i = 5
beginselect @key5 = index_col(@objname, @indid, @i)
end
else
if @i = 6
beginselect @key6 = index_col(@objname, @indid, @i)
end
else
if @i = 7
beginselect @key7 = index_col(@objname, @indid, @i)
end
else
if @i = 8
beginselect @key8 = index_col(@objname, @indid, @i)
end
else
if @i = 9
beginselect @key9 = index_col(@objname, @indid, @i)
end
else
if @i = 10
beginselect @key10 = index_col(@objname, @indid, @i)
end
elseif @i = 11
beginselect @key11 = index_col(@objname, @indid, @i)
end
else
if @i = 12
beginselect @key12 = index_col(@objname, @indid, @i)
end
else
if @i = 13
beginselect @key13 = index_col(@objname, @indid, @i)
end
else
if @i = 14
beginselect @key14 = index_col(@objname, @indid, @i)
end
else
if @i = 15
beginselect @key15 = index_col(@objname, @indid, @i)
end
else
if @i = 16
beginselect @key16 = index_col(@objname, @indid, @i)
end

/*
** Increment @i so it will check for the next key.
*/
select @i = @i + 1

end
/*
** When we get here we now have all the keys.
*/
keysdone:
set nocount off

/*
** Figure out if it's a clustered or nonclustered index.
*/
if @indid = 1
select @clustered = 1

if @indid > 1
select @clustered = 0

/*
** Now we'll check out the status bits for this index
*/

/*
** See if the index is unique (0x02).
*/
if exists (select *
from master.dbo.spt_values v, sysindexes i
where i.status & v.number = v.number
and v.type = 'I'
and v.number = 2
and i.id = @objid
and i.indid = @indid)
select @unique = 1
else
select @unique = 0

/*
** Now we have all the needed info for the index so we'll add
** the goods to the temporary table.
*/
insert into #spindtab
select name, @i - 1, @key1, @key2, @key3, @key4,
@key5, @key6, @key7, @key8, @key9,
@key10, @key11, @key12, @key13, @key14,
@key15, @key16, @unique, @clustered, status, status2
from sysindexes
where id = @objid
and indid = @indid
/*
** Now move @indid to the next index.
*/
select @lastindid = @indid
select @indid = null
select @indid = min(indid)
from sysindexes
where id = @objid
and indid > @lastindid
and indid < 255
end

/*
** Now print out the contents of the temporary index table.
*/
select index_name, index_num, index_key1, index_key2,
index_key3, index_key4, index_key5, index_key6,
index_key7, index_key8, index_key9, index_key10,
index_key11, index_key12, index_key13, index_key14,
index_key15, index_key16, index_unique, index_clustered,
index_status, index_status2
from #spindtab

drop table #spindtab
/* ptrack 325579 reset isolation 0 after we're done with procedure */
if @isolevel = 0
begin
set transaction isolation level 0
end
go
IF OBJECT_ID('dbo.sp_pb60index') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb60index >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60index >>>'
go
GRANT EXECUTE ON dbo.sp_pb60index TO public
go

IF OBJECT_ID('dbo.sp_pb60pkcheck') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb60pkcheck
IF OBJECT_ID('dbo.sp_pb60pkcheck') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60pkcheck >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb60pkcheck >>>'
END
go
create procedure sp_pb60pkcheck
@objname varchar(92)
as
declare @stat int
select @stat = sysstat2
from dbo.sysobjects
where id = object_id(@objname) and
(sysstat2 & 8) = 8
if (@stat is null)
begin
return (0)
end
else
begin
return (1)
end
go
IF OBJECT_ID('dbo.sp_pb60pkcheck') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb60pkcheck >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60pkcheck >>>'
go
GRANT EXECUTE ON dbo.sp_pb60pkcheck TO public
go

IF OBJECT_ID('dbo.sp_pb60primarykey') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb60primarykey
IF OBJECT_ID('dbo.sp_pb60primarykey') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60primarykey >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb60primarykey >>>'
END
go
create proc sp_pb60primarykey
@objname varchar(92)
as
declare @objid int /* the object id of the table */
declare @keyname varchar(30) /* name of primary key */
declare @indid int /* the index id of the index */
declare @keycnt smallint /* number of columns in pk */

select @objid = object_id(@objname)
if @objid is null
begin
return 1
end

select @keyname = name,
@indid = indid,
@keycnt = keycnt
from dbo.sysindexes
where id = object_id(@objname) and
indid > 0 and /* make sure it is an index */
(status2 & 2) = 2 and /* make sure declaritive constraint */
(status & 2048) = 2048 /* make sure it is primary key */

if @keycnt is null
begin
return 1
end
/* keycnt contains #clustered key columns but it contains #keys + 1 */
/* for non-clustered indexes. */
if @indid > 1
begin
select @keycnt = @keycnt - 1
end
if @keycnt = 0
begin
return 1
end

select @keyname, @keycnt,
index_col(@objname, @indid, 1),
index_col(@objname, @indid, 2),
index_col(@objname, @indid, 3),
index_col(@objname, @indid, 4),
index_col(@objname, @indid, 5),
index_col(@objname, @indid, 6),
index_col(@objname, @indid, 7),
index_col(@objname, @indid, 8),
index_col(@objname, @indid, 9),
index_col(@objname, @indid, 10),
index_col(@objname, @indid, 11),
index_col(@objname, @indid, 12),
index_col(@objname, @indid, 13),
index_col(@objname, @indid, 14),
index_col(@objname, @indid, 15),
index_col(@objname, @indid, 16)
go
IF OBJECT_ID('dbo.sp_pb60primarykey') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb60primarykey >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60primarykey >>>'
go
GRANT EXECUTE ON dbo.sp_pb60primarykey TO public
go

IF OBJECT_ID('dbo.sp_pb60procdesc') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb60procdesc
IF OBJECT_ID('dbo.sp_pb60procdesc') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60procdesc >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb60procdesc >>>'
END
go
create proc sp_pb60procdesc
@procid int = null ,
@procnumber smallint = null
as
select name, type, length, colid, prec, scale from dbo.syscolumns
where (id = @procid and number = @procnumber)
return
go
IF OBJECT_ID('dbo.sp_pb60procdesc') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb60procdesc >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60procdesc >>>'
go
GRANT EXECUTE ON dbo.sp_pb60procdesc TO public
go

IF OBJECT_ID('dbo.sp_pb60proclist') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb60proclist
IF OBJECT_ID('dbo.sp_pb60proclist') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60proclist >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb60proclist >>>'
END
go
create proc sp_pb60proclist
@sysprocs int = 1
as
declare @currdb varchar(31)

if @sysprocs = 0
begin
select o.id, o.name, o.uid, user_name(o.uid), c.number, 1
from dbo.sysobjects o, dbo.syscomments c
where o.type = 'P' and o.id = c.id and c.colid = 1 and c.colid2 = 0
order by 2, 4, 5, 6
end
else
begin
select @currdb = db_name(db_id())
if @currdb = 'sybsystemprocs'
begin
select o.id, o.name, o.uid, user_name(o.uid), c.number, 1
from dbo.sysobjects o, dbo.syscomments c
where o.type = 'P' and o.id = c.id and c.colid = 1 and c.colid2 = 0
union
select o.id, o.name, o.uid, user_name(o.uid), c.number, 3
from master.dbo.sysobjects o, master.dbo.syscomments c
where o.type = 'P' and o.id = c.id and c.colid = 1 and c.colid2 = 0
and substring(o.name, 1, 3) = 'sp_'
order by 2, 4, 5, 6
end
else
if @currdb = 'master'
begin
select o.id, o.name, o.uid, user_name(o.uid), c.number, 1
from dbo.sysobjects o, dbo.syscomments c
where o.type = 'P' and o.id = c.id and c.colid = 1 and c.colid2 = 0
union
select o.id, o.name, o.uid, user_name(o.uid), c.number, 2
from sybsystemprocs.dbo.sysobjects o,
sybsystemprocs.dbo.syscomments c
where o.type = 'P' and o.id = c.id and c.colid = 1 and c.colid2 = 0
and substring(o.name, 1, 3) = 'sp_'
order by 2, 4, 5, 6
end
else
begin
select o.id, o.name, o.uid, user_name(o.uid), c.number, 1
from dbo.sysobjects o, dbo.syscomments c
where o.type = 'P' and o.id = c.id and c.colid = 1 and c.colid2 = 0
union
select o.id, o.name, o.uid, user_name(o.uid), c.number, 2
from sybsystemprocs.dbo.sysobjects o,
sybsystemprocs.dbo.syscomments c
where o.type = 'P' and o.id = c.id and c.colid = 1 and c.colid2 = 0
and substring(o.name, 1, 3) = 'sp_'
union
select o.id, o.name, o.uid, user_name(o.uid), c.number, 3
from master.dbo.sysobjects o, master.dbo.syscomments c
where o.type = 'P' and o.id = c.id and c.colid = 1 and c.colid2 = 0
and substring(o.name, 1, 3) = 'sp_'
order by 2, 4, 5, 6
end
end
return
go
IF OBJECT_ID('dbo.sp_pb60proclist') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb60proclist >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60proclist >>>'
go
GRANT EXECUTE ON dbo.sp_pb60proclist TO public
go

IF OBJECT_ID('dbo.sp_pb60table') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb60table
IF OBJECT_ID('dbo.sp_pb60table') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb60table >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb60table >>>'
END
go
create procedure sp_pb60table
@table_name varchar(32) = null,
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@table_type varchar(100) = null
as
declare @type1 varchar(3)

if @table_type is null
begin
/* Select all ODBC supported data types */
select @type1 = 'SUV'
end
else
begin
/* TableType is case sensitive if CS server */
select @type1 = null
/* Add System Tables */
if (charindex("'SYSTEM TABLE'", @table_type) != 0)
select @type1 = @type1 + 'S'
/* Add User Tables */
if (charindex ("'TABLE'", @table_type) != 0)
select @type1 = @type1 + 'U'
/* Add Views */
if (charindex ("'VIEW'", @table_type) != 0)
select @type1 = @type1 + 'V'
end

if @table_name is null
begin
/* If table name not supplied, match all */
select @table_name = '%'
end
else
begin
if (@table_owner is null) and (charindex('%', @table_name) = 0)
begin
/* If owner not specified and table is specified */
if exists (select * from sysobjects where uid = user_id() and
name = @table_name and (type = 'U' or type = 'V' or type = 'S'))
begin
/* Override supplied owner w/owner of table */
select @table_owner = user_name()
end
end
end
/* If no owner supplied, force wildcard */
if @table_owner is null
select @table_owner = '%'

select o.name, o.id, o.type, o.uid, user_name(o.uid)
from sysobjects o
where o.name like @table_name
and user_name(o.uid) like @table_owner
and charindex(substring(o.type,1,1),@type1) != 0
go
IF OBJECT_ID('dbo.sp_pb60table') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb60table >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb60table >>>'
go
GRANT EXECUTE ON dbo.sp_pb60table TO public
go

IF OBJECT_ID('dbo.sp_pb80column') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80column
IF OBJECT_ID('dbo.sp_pb80column') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80column >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80column >>>'
END
go
/*------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80column lists the columns in a table. */
/* The objectid is required as arg1. */
/*------------------------------------------------*/
create proc sp_pb80column
@id int

as
declare @text varchar(255)
select @text = null
select c.colid, c.status, c.type, c.length, c.name, c.usertype,
c.prec, c.scale, @text
from dbo.syscolumns c where c.id = @id and c.cdefault = 0
union select
c.colid, c.status, c.type, c.length, c.name, c.usertype,
c.prec, c.scale, m.text
from dbo.syscolumns c, dbo.syscomments m where c.id = @id
and c.cdefault = m.id and m.colid = 1
order by 1
go
IF OBJECT_ID('dbo.sp_pb80column') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80column >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80column >>>'
go
GRANT EXECUTE ON dbo.sp_pb80column TO public
go

IF OBJECT_ID('dbo.sp_pb80db') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80db
IF OBJECT_ID('dbo.sp_pb80db') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80db >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80db >>>'
END
go
/*-----------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80db retrieves the names of all databases */
/* available for this server. */
/*-----------------------------------------------------*/
create proc sp_pb80db as
select name from master.dbo.sysdatabases
go
IF OBJECT_ID('dbo.sp_pb80db') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80db >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80db >>>'
go
GRANT EXECUTE ON dbo.sp_pb80db TO public
go

IF OBJECT_ID('dbo.sp_pb80extcat') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80extcat
IF OBJECT_ID('dbo.sp_pb80extcat') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80extcat >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80extcat >>>'
END
go
/*-------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80extcat checks the status of PB Catalog. */
/* Returns cExists[10] with Y/N values. */
/*-------------------------------------------------*/
create procedure sp_pb80extcat
as
declare @tbl char(1)
declare @col char(1)
declare @fmt char(1)
declare @vld char(1)
declare @edt char(1)
declare @tblproc char(1)
declare @colproc char(1)
declare @fmtproc char(1)
declare @vldproc char(1)
declare @edtproc char(1)
declare @existbuff char(10)
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pbcattbl'))
select @tbl = 'Y'
else
select @tbl = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pbcatcol'))
select @col = 'Y'
else
select @col = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pbcatfmt'))
select @fmt = 'Y'
else
select @fmt = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pbcatvld'))
select @vld = 'Y'
else
select @vld = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pbcatedt'))
select @edt = 'Y'
else
select @edt = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pb_cattbl'))
select @tblproc = 'Y'
else
select @tblproc = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pb_catcol'))
select @colproc = 'Y'
else
select @colproc = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pb_catfmt'))
select @fmtproc = 'Y'
else
select @fmtproc = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pb_catvld'))
select @vldproc = 'Y'
else
select @vldproc = 'N'
if exists
(select id from dbo.sysobjects where
id = object_id('dbo.pb_catedt'))
select @edtproc = 'Y'
else
select @edtproc = 'N'
select @existbuff = @tbl + @col + @fmt + @vld + @edt +
@tblproc + @colproc + @fmtproc +
@vldproc + @edtproc
select @existbuff
go
IF OBJECT_ID('dbo.sp_pb80extcat') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80extcat >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80extcat >>>'
go
GRANT EXECUTE ON dbo.sp_pb80extcat TO public
go

IF OBJECT_ID('dbo.sp_pb80fktable') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80fktable
IF OBJECT_ID('dbo.sp_pb80fktable') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80fktable >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80fktable >>>'
END
go
/*--------------------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80fktable lists the tables that reference this table. */
/*--------------------------------------------------------------*/
create procedure sp_pb80fktable
@objname varchar(61) = null
as
declare @objid int
declare @isolevel int /* ptrack 325579 isolation level */

if (@objname is null)
return (1)

select @objid = object_id(@objname)
/* ptrack 325579 override isolation level 0 default */
select @isolevel = @@isolation
if @isolevel = 0
begin
set transaction isolation level 1
end

select o.name, o.id, o.type, o.uid, user_name(o.uid)
from dbo.sysobjects o, dbo.sysreferences r
where r.reftabid = @objid and
r.tableid = o.id

if @isolevel = 0
begin
set transaction isolation level 0
end
go
IF OBJECT_ID('dbo.sp_pb80fktable') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80fktable >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80fktable >>>'
go
GRANT EXECUTE ON dbo.sp_pb80fktable TO public
go

IF OBJECT_ID('dbo.sp_pb80foreignkey') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80foreignkey
IF OBJECT_ID('dbo.sp_pb80foreignkey') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80foreignkey >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80foreignkey >>>'
END
go
/*-----------------------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80foreignkey lists all foreign keys associated with */
/* a table whose name is passed as arg1 (required). */
/*-----------------------------------------------------------------*/
create proc sp_pb80foreignkey
@objname varchar(92)
as
declare @objid int /* the object id of the fk table */
declare @keyname varchar(30) /* name of foreign key */
declare @constid int /* the constraint id in sysconstraints */
declare @keycnt smallint /* number of columns in pk */
declare @stat int
declare @isolevel int /* ptrack 325579 isolation level */

select @objid = object_id(@objname)
if (@objid is null)
begin
return (1)
end
select @stat = sysstat2
from dbo.sysobjects
where id = @objid and
(sysstat2 & 2) = 2
if (@stat is null)
begin
return (1)
end

/* Now I know this table has one or more foreign keys. */
/* ptrack 325579 override isolation level 0 default */
select @isolevel = @@isolation
if @isolevel = 0
begin
set transaction isolation level 1
end

select o1.name, r.keycnt, o2.name, user_name(o2.uid),
r.fokey1, r.fokey2, r.fokey3, r.fokey4, r.fokey5, r.fokey6,
r.fokey7, r.fokey8, r.fokey9, r.fokey10, r.fokey11, r.fokey12,
r.fokey13, r.fokey14, r.fokey15, r.fokey16
from dbo.sysconstraints c, dbo.sysobjects o1,
dbo.sysreferences r, dbo.sysobjects o2
where c.tableid = @objid and
c.status = 64 and
c.constrid = o1.id and
o1.type = 'RI' and
c.constrid = r.constrid and
r.reftabid = o2.id

if @isolevel = 0
begin
set transaction isolation level 0
end
go
IF OBJECT_ID('dbo.sp_pb80foreignkey') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80foreignkey >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80foreignkey >>>'
go
GRANT EXECUTE ON dbo.sp_pb80foreignkey TO public
go

IF OBJECT_ID('dbo.sp_pb80index') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80index
IF OBJECT_ID('dbo.sp_pb80index') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80index >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80index >>>'
END
go
/*-----------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80index retrieves info about all indexes for */
/* a specific table (@objname is required). */
/*-----------------------------------------------------*/
create procedure sp_pb80index
@objname varchar(92) /* the table to check for indexes */
as
declare @objid int /* the object id of the table */
declare @indid int /* the index id of an index */
declare @key1 varchar(30) /* first key */
declare @key2 varchar(30) /* second key */
declare @key3 varchar(30) /* third key */
declare @key4 varchar(30) /* fourth key */
declare @key5 varchar(30) /* ... */
declare @key6 varchar(30)
declare @key7 varchar(30)
declare @key8 varchar(30)
declare @key9 varchar(30) /* ... */
declare @key10 varchar(30)
declare @key11 varchar(30)
declare @key12 varchar(30)
declare @key13 varchar(30) /* ... */
declare @key14 varchar(30)
declare @key15 varchar(30)
declare @key16 varchar(30)

declare @unique smallint /* index is unique */
declare @clustered smallint /* index is clustered */
declare @isolevel int /* ptrack 325579 isolation level */
/*
** Check to see the the table exists and initialize @objid.
*/
select @objid = object_id(@objname)

/*
** Table doesn't exist so return.
*/
if @objid is null
begin
return
end

/*
** See if the object has any indexes.
** Since there may be more than one entry in sysindexes for the object,
** this select will set @indid to the index id of the first index.
*/
select @indid = min(indid)
from dbo.sysindexes
where id = @objid
and indid > 0
and indid < 255

/*
** If no indexes, return.
*/
if @indid is null
begin
return
end

/* ptrack 325579 override isolation level 0 default */
select @isolevel = @@isolation
if @isolevel = 0
begin
set transaction isolation level 1
end

/*
** Now check out each index, figure out it's type and keys and
** save the info in a temporary table that we'll print out at the end.
*/
create table #spindtab
(
index_name varchar(30),
index_num int,
index_key1 varchar(30) null,
index_key2 varchar(30) null,
index_key3 varchar(30) null,
index_key4 varchar(30) null,
index_key5 varchar(30) null,
index_key6 varchar(30) null,
index_key7 varchar(30) null,
index_key8 varchar(30) null,
index_key9 varchar(30) null,
index_key10 varchar(30) null,
index_key11 varchar(30) null,
index_key12 varchar(30) null,
index_key13 varchar(30) null,
index_key14 varchar(30) null,
index_key15 varchar(30) null,
index_key16 varchar(30) null,
index_unique smallint,
index_clustered smallint,
index_status smallint,
index_status2 int
)

while @indid != null
begin

/*
** First we'll figure out what the keys are.
*/
declare @i int
declare @thiskey varchar(30)
declare @lastindid int

select @i = 1

set nocount on

while @i <= 16
begin
select @thiskey = index_col(@objname, @indid, @i)

if @thiskey = null
begin
goto keysdone
end

if @i = 1
begin
select @key1 = index_col(@objname, @indid, @i)
end
else
if @i = 2
begin
select @key2 = index_col(@objname, @indid, @i)
end
else
if @i = 3
begin
select @key3 = index_col(@objname, @indid, @i)
end
else
if @i = 4
begin
select @key4 = index_col(@objname, @indid, @i)
end
else
if @i = 5
begin
select @key5 = index_col(@objname, @indid, @i)
end
else
if @i = 6
begin
select @key6 = index_col(@objname, @indid, @i)
end
else
if @i = 7
begin
select @key7 = index_col(@objname, @indid, @i)
end
else
if @i = 8
begin
select @key8 = index_col(@objname, @indid, @i)
end
else
if @i = 9
begin
select @key9 = index_col(@objname, @indid, @i)
end
else
if @i = 10
begin
select @key10 = index_col(@objname, @indid, @i)
end
else
if @i = 11
begin
select @key11 = index_col(@objname, @indid, @i)
end
else
if @i = 12
begin
select @key12 = index_col(@objname, @indid, @i)
end
else
if @i = 13
begin
select @key13 = index_col(@objname, @indid, @i)
end
else
if @i = 14
begin
select @key14 = index_col(@objname, @indid, @i)
end
else
if @i = 15
begin
select @key15 = index_col(@objname, @indid, @i)
end
else
if @i = 16
begin
select @key16 = index_col(@objname, @indid, @i)
end

/*
** Increment @i so it will check for the next key.
*/
select @i = @i + 1

end

/*
** When we get here we now have all the keys.
*/
keysdone:
set nocount off

/*
** Figure out if it's a clustered or nonclustered index.
*/
if @indid = 1
select @clustered = 1

if @indid > 1
select @clustered = 0

/*
** Now we'll check out the status bits for this index
*/

/*
** See if the index is unique (0x02).
*/
if exists (select *
from master.dbo.spt_values v, dbo.sysindexes i
where i.status & v.number = v.number
and v.type = 'I'
and v.number = 2
and i.id = @objid
and i.indid = @indid)
select @unique = 1
else
select @unique = 0

/*
** Now we have all the needed info for the index so we'll add
** the goods to the temporary table.
*/
insert into #spindtab
select name, @i - 1, @key1, @key2, @key3, @key4,
@key5, @key6, @key7, @key8, @key9,
@key10, @key11, @key12, @key13, @key14,
@key15, @key16, @unique, @clustered, status, status2
from dbo.sysindexes
where id = @objid
and indid = @indid
/*
** Now move @indid to the next index.
*/
select @lastindid = @indid
select @indid = null
select @indid = min(indid)
from dbo.sysindexes
where id = @objid
and indid > @lastindid
and indid < 255
end

/*
** Now print out the contents of the temporary index table.
*/
select index_name, index_num, index_key1, index_key2,
index_key3, index_key4, index_key5, index_key6,
index_key7, index_key8, index_key9, index_key10,
index_key11, index_key12, index_key13, index_key14,
index_key15, index_key16, index_unique, index_clustered,
index_status, index_status2
from #spindtab

drop table #spindtab
/* ptrack 325579 reset isolation 0 after we're done with procedure */
if @isolevel = 0
begin
set transaction isolation level 0
end
go
IF OBJECT_ID('dbo.sp_pb80index') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80index >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80index >>>'
go
GRANT EXECUTE ON dbo.sp_pb80index TO public
go

IF OBJECT_ID('dbo.sp_pb80pkcheck') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80pkcheck
IF OBJECT_ID('dbo.sp_pb80pkcheck') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80pkcheck >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80pkcheck >>>'
END
go
/*----------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80pkcheck determines whether or not a table */
/* has a Primary Key. Table name is a required arg. */
/*----------------------------------------------------*/
create procedure sp_pb80pkcheck
@objname varchar(92)
as
declare @stat int
select @stat = sysstat2
from dbo.sysobjects
where id = object_id(@objname) and
(sysstat2 & 8) = 8
if (@stat is null)
begin
return (0)
end
else
begin
return (1)
end
go
IF OBJECT_ID('dbo.sp_pb80pkcheck') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80pkcheck >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80pkcheck >>>'
go
GRANT EXECUTE ON dbo.sp_pb80pkcheck TO public
go

IF OBJECT_ID('dbo.sp_pb80primarykey') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80primarykey
IF OBJECT_ID('dbo.sp_pb80primarykey') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80primarykey >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80primarykey >>>'
END
go
/*------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80primarykey lists the columns that */
/* comprise the primary key for a table. The */
/* table name is required as arg1. */
/*------------------------------------------------*/
create proc sp_pb80primarykey
@objname varchar(92)
as
declare @objid int /* the object id of the table */
declare @keyname varchar(30) /* name of primary key */
declare @indid int /* the index id of the index */
declare @keycnt smallint /* number of columns in pk */

select @objid = object_id(@objname)
if @objid is null
begin
return 1
end

select @keyname = name,
@indid = indid,
@keycnt = keycnt
from dbo.sysindexes
where id = object_id(@objname) and
indid > 0 and /* make sure it is an index */
(status2 & 2) = 2 and /* make sure declaritive constraint */
(status & 2048) = 2048 /* make sure it is primary key */

if @keycnt is null
begin
return 1
end
/* keycnt contains #clustered key columns but it contains #keys + 1 */
/* for non-clustered indexes. */
if @indid > 1
begin
select @keycnt = @keycnt - 1
end
if @keycnt = 0
begin
return 1
end

select @keyname, @keycnt,
index_col(@objname, @indid, 1),
index_col(@objname, @indid, 2),
index_col(@objname, @indid, 3),
index_col(@objname, @indid, 4),
index_col(@objname, @indid, 5),
index_col(@objname, @indid, 6),
index_col(@objname, @indid, 7),
index_col(@objname, @indid, 8),
index_col(@objname, @indid, 9),
index_col(@objname, @indid, 10),
index_col(@objname, @indid, 11),
index_col(@objname, @indid, 12),
index_col(@objname, @indid, 13),
index_col(@objname, @indid, 14),
index_col(@objname, @indid, 15),
index_col(@objname, @indid, 16)
go
IF OBJECT_ID('dbo.sp_pb80primarykey') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80primarykey >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80primarykey >>>'
go
GRANT EXECUTE ON dbo.sp_pb80primarykey TO public
go

IF OBJECT_ID('dbo.sp_pb80procdesc') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80procdesc
IF OBJECT_ID('dbo.sp_pb80procdesc') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80procdesc >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80procdesc >>>'
END
go
/*------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80procdesc gets a description of the arg */
/* list for a given stored procedure. */
/*------------------------------------------------*/
create proc sp_pb80procdesc
@procid int = null ,
@procnumber smallint = null
as
select name, type, length, colid, prec, scale from dbo.syscolumns
where (id = @procid and number = @procnumber)
return
go
IF OBJECT_ID('dbo.sp_pb80procdesc') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80procdesc >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80procdesc >>>'
go
GRANT EXECUTE ON dbo.sp_pb80procdesc TO public
go

IF OBJECT_ID('dbo.sp_pb80proclist') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80proclist
IF OBJECT_ID('dbo.sp_pb80proclist') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80proclist >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80proclist >>>'
END
go
/*------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80proclist lists available stored procs */
/* Enhanced to support SystemProcs='NO' DBParm. */
/* */
/* In order to support extended stored procs in */
/* ASE V11.5, check for xp added. */
/*------------------------------------------------*/
create proc sp_pb80proclist
@sysprocs int = 1
as
declare @currdb varchar(31)

if @sysprocs = 0
begin
select o.id, o.name, o.uid, user_name(o.uid), c.number, 1
from dbo.sysobjects o, dbo.syscomments c
where (o.type = 'P' or o.type = 'XP') and o.id = c.id and c.colid = 1 and c.colid2 = 0
order by 2, 4, 5, 6
end
else
begin
select @currdb = db_name(db_id())
if @currdb = 'sybsystemprocs'
begin
select o.id, o.name, o.uid, user_name(o.uid), c.number, 1
from dbo.sysobjects o, dbo.syscomments c
where (o.type = 'P' or o.type = 'XP') and o.id = c.id and c.colid = 1 and c.colid2 = 0
union
select o.id, o.name, o.uid, user_name(o.uid), c.number, 3
from master.dbo.sysobjects o, master.dbo.syscomments c
where (o.type = 'P' or o.type = 'XP') and o.id = c.id and c.colid = 1 and c.colid2 = 0
and (substring(o.name, 1, 3) = 'sp_' or
substring(o.name, 1, 3) = 'xp_')
order by 2, 4, 5, 6
end
else
if @currdb = 'master'
begin
select o.id, o.name, o.uid, user_name(o.uid), c.number, 1
from dbo.sysobjects o, dbo.syscomments c
where (o.type = 'P' or o.type = 'XP') and o.id = c.id and c.colid = 1 and c.colid2 = 0
union
select o.id, o.name, o.uid, user_name(o.uid), c.number, 2
from sybsystemprocs.dbo.sysobjects o,
sybsystemprocs.dbo.syscomments c
where (o.type = 'P' or o.type = 'XP') and o.id = c.id and c.colid = 1 and c.colid2 = 0
and (substring(o.name, 1, 3) = 'sp_' or
substring(o.name, 1, 3) = 'xp_')
order by 2, 4, 5, 6
end
else
begin
select o.id, o.name, o.uid, user_name(o.uid), c.number, 1
from dbo.sysobjects o, dbo.syscomments c
where (o.type = 'P' or o.type = 'XP') and o.id = c.id and c.colid = 1 and c.colid2 = 0
union
select o.id, o.name, o.uid, user_name(o.uid), c.number, 2
from sybsystemprocs.dbo.sysobjects o,
sybsystemprocs.dbo.syscomments c
where (o.type = 'P' or o.type = 'XP') and o.id = c.id and c.colid = 1 and c.colid2 = 0
and (substring(o.name, 1, 3) = 'sp_' or
substring(o.name, 1, 3) = 'xp_')
union
select o.id, o.name, o.uid, user_name(o.uid), c.number, 3
from master.dbo.sysobjects o, master.dbo.syscomments c
where (o.type = 'P' or o.type = 'XP') and o.id = c.id and c.colid = 1 and c.colid2 = 0
and (substring(o.name, 1, 3) = 'sp_' or
substring(o.name, 1, 3) = 'xp_')
order by 2, 4, 5, 6
end
end
return
go
IF OBJECT_ID('dbo.sp_pb80proclist') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80proclist >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80proclist >>>'
go
GRANT EXECUTE ON dbo.sp_pb80proclist TO public
go

IF OBJECT_ID('dbo.sp_pb80table') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80table
IF OBJECT_ID('dbo.sp_pb80table') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80table >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80table >>>'
END
go
/*---------------------------------------------------------*/
/* This version of sp_pb80table displays all tables, */
/* including those for which the current user has no */
/* permissions. */
/* An alternate version of this procedure exists in */
/* pbsyc2.sql which restricts the table list for security */
/* reasons. */
/* It is up to the DBA at your site to decide which */
/* version of sp_pb80table should be implemented. */
/*---------------------------------------------------------*/
create procedure sp_pb80table
@table_name varchar(32) = null,
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@table_type varchar(100) = null
as
declare @type1 varchar(3)

if @table_type is null
begin
/* Select all ODBC supported data types */
select @type1 = 'SUV'
end
else
begin
/* TableType is case sensitive if CS server */
select @type1 = null
/* Add System Tables */
if (charindex("'SYSTEM TABLE'", @table_type) != 0)
select @type1 = @type1 + 'S'
/* Add User Tables */
if (charindex ("'TABLE'", @table_type) != 0)
select @type1 = @type1 + 'U'
/* Add Views */
if (charindex ("'VIEW'", @table_type) != 0)
select @type1 = @type1 + 'V'
end

if @table_name is null
begin
/* If table name not supplied, match all */
select @table_name = '%'
end
else
begin
if (@table_owner is null) and (charindex('%', @table_name) = 0)
begin
/* If owner not specified and table is specified */
if exists (select * from sysobjects where uid = user_id() and
name = @table_name and (type = 'U' or type = 'V' or type = 'S'))
begin
/* Override supplied owner w/owner of table */
select @table_owner = user_name()
end
end
end
/* If no owner supplied, force wildcard */
if @table_owner is null
select @table_owner = '%'

select o.name, o.id, o.type, o.uid, user_name(o.uid)
from sysobjects o
where o.name like @table_name
and user_name(o.uid) like @table_owner
and charindex(substring(o.type,1,1),@type1) != 0
go
IF OBJECT_ID('dbo.sp_pb80table') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80table >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80table >>>'
go
GRANT EXECUTE ON dbo.sp_pb80table TO public
go

IF OBJECT_ID('dbo.sp_pb80text') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_pb80text
IF OBJECT_ID('dbo.sp_pb80text') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_pb80text >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_pb80text >>>'
END
go
/*-----------------------------------------------------*/
/* PowerBuilder Client Library Interface */
/* sp_pb80text retrieves the text of a stored */
/* procedure from the syscomments table. requires */
/* and @objid argument and an optional @number arg */
/*-----------------------------------------------------*/
create procedure sp_pb80text
@objid int ,
@number smallint = null,
@db smallint
as
if (@number = null)
select text from dbo.syscomments where id = @objid
else
begin
if @db = 1
begin
select text from dbo.syscomments where
(id = @objid and number = @number)
end
else
if @db = 2
begin
select text from sybsystemprocs.dbo.syscomments where
(id = @objid and number = @number)
end
else
if @db = 3
begin
select text from master.dbo.syscomments where
(id = @objid and number = @number)
end
end
return
go
IF OBJECT_ID('dbo.sp_pb80text') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_pb80text >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_pb80text >>>'
go
GRANT EXECUTE ON dbo.sp_pb80text TO public
go[/spoiler]

Чтобы созданные вами программы на PowerBuilder 6.5.1 были похожи на современные под WinXP & Vista интерфейс (закругленные углы у кнопок и т.п.) нужно создать текстовый файлик с именем: иемя_вашей_программы.exe.manifest и положить его в директорию, где лежит ваш EXE файл, а внутрь вложить вот этот текст:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1" manifestVersion="1.0">
<assemblyIdentityversion="1.0.0.0"
processorArchitecture="X86"
name="gic.gic"
type="win32"
/>
<description>gic</description>
<dependency><dependentAssembly><assemblyIdentitytype="win32"
name="Microsoft.Windows.Common-Controls"
version="6.0.0.0"
processorArchitecture="X86"
publicKeyToken="6595b64144ccf1df"
language="*"
/>
</dependentAssembly>
</dependency>
</assembly>

Например, чтобы PowerBuilder 6.5.1 был немного красивее, кнопочки стали с закругленными углами, по умолчанию он запускается из директории C:\Sybase\PB6\pb60.exe, то ваш файл будет называться C:\Sybase\PB6\pb60.exe.manifest