if (exists (select * from sysobjects

  where name = 'spt_datatype_info' and type = 'U'))

    drop table spt_datatype_info

go

create table spt_datatype_info

(

 ss_dtype    tinyint not null,

 TYPE_NAME          varchar(30)  not null,

 DATA_TYPE    smallint not null,

 typelength         int          not null,

 LITERAL_PREFIX     varchar(32)  null,

 LITERAL_SUFFIX     varchar(32)  null,

 CREATE_PARAMS      varchar(32)  null,

 NULLABLE           smallint     not null,

 CASE_SENSITIVE     smallint     not null,

 SEARCHABLE         smallint     not null,

 UNSIGNED_ATTRIBUTE smallint     null,

 FIXED_PREC_SCALE   smallint     not null,

 -- MONEY              smallint     not null,

 AUTO_INCREMENT     smallint     null,

 LOCAL_TYPE_NAME    varchar(128) not null,

 MINIMUM_SCALE    smallint null,

 MAXIMUM_SCALE    smallint null,

 SQL_DATA_TYPE    smallint null,

 SQL_DATETIME_SUB   smallint null,

 NUM_PREC_RADIX    smallint null

 -- INTERVAL_PRECISION smallint null

)

go

grant select on spt_datatype_info to PUBLIC

go

insert into spt_datatype_info values

(48, 'tinyint', -6, 3, NULL, NULL, NULL, 1, 0, 2, 1, 0, 0, 'tinyint',

NULL, NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(34, 'image', -4, 2147483647, '0x', NULL, NULL, 1, 0, 1, NULL, 0, NULL,

'image', NULL, NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(37, 'varbinary', -3, 255, '0x', NULL, 'max length', 1, 0, 2, NULL, 0,

NULL, 'varbinary', NULL, NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(45, 'binary', -2, 255, '0x', NULL, 'length', 1, 0, 2, NULL, 0, NULL,

'binary', NULL, NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(35, 'text', -1, 2147483647, '''', '''', NULL, 1, 1, 1, NULL, 0, NULL,

'text', NULL, NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(47, 'char', 1, 255, '''', '''', 'length', 1, 1, 3, NULL, 0, NULL,

'char', NULL, NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(63, 'numeric', 2, 38, NULL, NULL, 'precision,scale', 1, 0, 2, 0, 0, 0,

'numeric', 0, 38, NULL, NULL, NULL)

insert into spt_datatype_info values

(55, 'decimal', 3, 38, NULL, NULL, 'precision,scale', 1, 0, 2, 0, 0, 0,

'decimal', 0, 38, NULL, NULL, NULL)

insert into spt_datatype_info values

(60, 'money', 3, 12, '$', NULL, NULL, 1, 0, 2, 0, 1, 0, 'money', NULL,

NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(122, 'smallmoney', 3, 8, '$', NULL, NULL, 1, 0, 2, 0, 1, 0,

'smallmoney', NULL, NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(56, 'int', 4, 4, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, 'int', NULL, NULL,

NULL, NULL, NULL)

insert into spt_datatype_info values

(52, 'smallint', 5, 2, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, 'smallint',

NULL, NULL, NULL, NULL, NULL)

insert into spt_datatype_info values

(62, 'float', 8, 8, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, 'double', NULL,

NULL, NULL, NULL, 10)

insert into spt_datatype_info values

(59, 'real', 7, 7, NULL, NULL, NULL, 1, 0, 2, 0, 0, 0, 'real', NULL,

NULL, NULL, NULL, 10)

insert into spt_datatype_info values

(61, 'datetime', 93, 23, '''', '''', NULL, 1, 0, 3, NULL, 0, NULL,

'datetime', NULL, NULL, 93, NULL, NULL)

insert into spt_datatype_info values

(58, 'smalldatetime', 93, 16, '''', '''', NULL, 1, 0, 3, NULL, 0, NULL,

'smalldatetime', NULL, NULL, 93, NULL, NULL)

insert into spt_datatype_info values

(39, 'varchar', 12, 255, '''', '''', 'max length', 1, 1, 3, NULL, 0,

NULL, 'varchar', NULL, NULL, NULL, NULL, NULL)

go

if exists (select * from sysobjects where name =

    'sp_jdbc_function_escapes')

    begin

        drop procedure sp_jdbc_function_escapes

    end

go

create procedure sp_jdbc_function_escapes

as

    select * from DBA.jdbc_function_escapes

go

grant execute on sp_jdbc_function_escapes to PUBLIC

go

/*

**   sp_jdbc_tables

**

*/

if exists (select * from sysobjects where name = 'sp_jdbc_tables')

begin

    drop procedure sp_jdbc_tables

end

go

create procedure sp_jdbc_tables

 @table_name  varchar(128)  = null,

 @table_owner varchar(128)  = null,

 @table_qualifier        varchar(128)  = null,

 @table_type  varchar(64) = null

as

declare @id int

declare @searchstr char(10)

if @table_name is null select @table_name = '%'

select @id = id from sysobjects where name like @table_name

if (@id is null)

begin

 	raiserror 17461 'Table does not exist'

	return (3)

end

if (patindex('%table%',lcase(@table_type)) > 0)

  	select @table_type = 'base'

if (patindex('%base%',lcase(@table_type)) > 0)

	select @searchstr = 'base'

else if (patindex('%view%',lcase(@table_type)) > 0)

	select @searchstr = 'view' 

else if @table_type is null

 	select @searchstr = '%'

else

begin

	raiserror 99998 'Only valid table types are TABLE, BASE, VIEW or null'

	return(3)

end

if @table_owner is null select @table_owner = '%'

select

   TABLE_CAT = 	substring(db_name(),1,length(db_name())),

   TABLE_SCHEM=	substring(user_name(creator),1,length(user_name(creator))),

   TABLE_NAME = substring(table_name,1,length(table_name)),

   TYBLE_TYPE = substring(table_type,1,length(table_type)),

   REMARKS=	remarks

from systable where table_name like @table_name and

user_name(creator) like @table_owner and table_type like @searchstr

order by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME

go