home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
On Hand
/
On_Hand_From_Softbank_1994_Release_2_Disc_2_1994.iso
/
00202
/
s
/
disk4
/
instcat.sq_
/
instcat.bin
Wrap
Text File
|
1993-04-28
|
94KB
|
3,162 lines
/*
NOTE: you MUST change the last row inserted into spt_server_info
to be version number of this file. the convention is VV.vvvv, where
VV is major version number ("00" until we ship), and vvvv is minor
version number in the form of month and day (mmdd) of the date you
check in this file. add 12 to the month to keep in sync with the
driver version numbers. e.g. checking in on feb 5 would mean setting
the value to 00.1405.
NOTE: The version number will be set automatically when you run
BUILD VER from the root of the SLM tree.
*/
/****************************************************************************/
/* This portion sets up the ability to perform all the functions in this */
/* script */
/****************************************************************************/
use master
go
dump tran master with truncate_only
go
if exists (select * from sysobjects
where name = 'sp_configure' and sysstat & 7 = 4)
begin
execute sp_configure 'update',1
end
reconfigure with override
go
/*
** If old versions of tables exist, drop them.
*/
if (exists (select * from sysobjects where name = 'MSdatatype_info'))
drop table MSdatatype_info
go
if (exists (select * from sysobjects where name = 'MSdatatype_info_ext'))
drop table MSdatatype_info_ext
go
if (exists (select * from sysobjects where name = 'MStable_types'))
drop table MStable_types
go
if (exists (select * from sysobjects where name = 'MSserver_info'))
drop table MSserver_info
go
/*
** If tables already exist, drop them.
*/
if (exists (select * from sysobjects where name = 'spt_datatype_info'))
drop table spt_datatype_info
go
if (exists (select * from sysobjects where name = 'spt_datatype_info_ext'))
drop table spt_datatype_info_ext
go
if (exists (select * from sysobjects where name = 'spt_table_types'))
drop table spt_table_types
go
if (exists (select * from sysobjects where name = 'spt_server_info'))
drop table spt_server_info
go
if (exists (select * from sysobjects where name = 'sp_tables'))
drop proc sp_tables
go
if (exists (select * from sysobjects where name = 'sp_statistics'))
drop proc sp_statistics
go
if (exists (select * from sysobjects where name = 'sp_columns'))
drop proc sp_columns
go
if (exists (select * from sysobjects where name = 'sp_fkeys'))
drop proc sp_fkeys
go
if (exists (select * from sysobjects where name = 'sp_pkeys'))
drop proc sp_pkeys
dump tran master with truncate_only
go
go
if (exists (select * from sysobjects where name = 'sp_stored_procedures'))
drop proc sp_stored_procedures
go
if (exists (select * from sysobjects where name = 'sp_sproc_columns'))
drop proc sp_sproc_columns
go
if (exists (select * from sysobjects where name = 'sp_table_privileges'))
drop proc sp_table_privileges
go
if (exists (select * from sysobjects where name = 'sp_column_privileges'))
drop proc sp_column_privileges
go
if (exists (select * from sysobjects where name = 'sp_server_info'))
drop proc sp_server_info
go
if (exists (select * from sysobjects where name = 'sp_datatype_info'))
drop proc sp_datatype_info
go
if (exists (select * from sysobjects where name = 'sp_special_columns'))
drop proc sp_special_columns
go
if (exists (select * from sysobjects where name = 'sp_databases'))
drop proc sp_databases
go
dump tran master with truncate_only
go
print "creating table spt_datatype_info_ext"
go
create table spt_datatype_info_ext (user_type smallint not null,
create_params varchar(32) null)
go
grant select on spt_datatype_info_ext to public
go
insert into spt_datatype_info_ext
/* CHAR user_type, create_params */
values (1, "length" )
insert into spt_datatype_info_ext
/* VARCHAR user_type, create_params */
values (2, "max length" )
insert into spt_datatype_info_ext
/* BINARY user_type, create_params */
values (3, "length" )
insert into spt_datatype_info_ext
/* VARBINARY user_type, create_params */
values (4, "max length" )
insert into spt_datatype_info_ext
/* SYSNAME user_type, create_params */
values (18, "max length" )
go
print "creating table spt_datatype_info"
go
create table spt_datatype_info (ss_dtype tinyint not null,
type_name varchar(32) not null,
data_type smallint not null,
data_precision int null,
numeric_scale smallint null,
numeric_radix smallint null,
length int 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,
money smallint not null,
auto_increment smallint null,
local_type_name varchar(128) not null,
aux int null)
go
grant select on spt_datatype_info to public
go
/* Get case sensitivity */
if 'A' = 'A'
begin
declare @case smallint
select @case = 0
select @case = 1 where 'a' != 'A'
insert into spt_datatype_info
/* ss_type,name, data_type,prec,scale,rdx, len, prf, suf, cp, nul,case,srch,unsigned,money,auto, local, aux */
values (45,"binary",-2, null,null, null,null,"0x",null,"length",1, 0, 2, null, 0, null, /* Local Binary */ "binary", 0)
insert into spt_datatype_info
/* ss_type,name, data_type, prec,scale,rdx,len, prf, suf, cp, nul, case,srch,unsigned,money,auto, local, aux */
values (50,"bit",-7, 1, 0, 2, null, null,null,null,0, 0, 2, null, 0, null, /* Local Bit */ "bit", 0)
insert into spt_datatype_info
/* ss_type,name, data_type,prec,scale,rdx, len, prf, suf, cp, nul,case, srch,unsigned,money,auto, local, aux */
values (47,"char",1, null,null, null,null,"'", "'", "length",1, @case, 3, null, 0, null, /* Local Char */ "char", 0)
insert into spt_datatype_info
/* ss_type,name, data_type,prec,scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto, local, aux */
values (61,"datetime",11, 23, 3, 10, 16, "'", "'", null, 1, 0, 3, null, 0, null, /* Local Datetime */ "datetime", 0)
insert into spt_datatype_info
/* ss_type,name, data_type,prec,scale,rdx,len, prf, suf, cp, nul, case,srch,unsigned,money,auto, local, aux */
values (58,"smalldatetime",11, 16, 0, 10, 16, "'", "'", null, 1, 0, 3, null, 0, null, /* Local Smalldatetime */ "smalldatetime", 0)
insert into spt_datatype_info /* sql server type is "datetimn" */
/* ss_type, name, data_type,prec, scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto, local, aux */
values (111,"smalldatetime",0, 0, 0, 10, 0, "'", "'", null, 1, 0, 3, null, 0, null, /* Local Datetimn */ "datetime", null)
insert into spt_datatype_info
/* ss_type, name, data_type,prec,scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (62,"float",6, 15, null, 10, null,null,null,null,1, 0, 2, 0, 0, 0, /* Local Float */ "float", 0)
insert into spt_datatype_info /* sql server type is "floatn" */
/* ss_type, name, data_type,prec,scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (109,"float real",0, 0, null, 10, 0, null,null,null, 1, 0, 2, 0, 0, 0, /* Local RealFloat */ "real float", null)
insert into spt_datatype_info
/* ss_type, name, data_type,prec,scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (59, "real",7, 7, null, 10, null, null,null,null, 1, 0, 2, 0, 0, 0, /* Local Real */ "real", 0)
insert into spt_datatype_info
/* ss_type, name, data_type,prec, scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values(122, "smallmoney",3, 10, 4, 10, null, "$", null,null, 1, 0, 2, 0, 1, 0, /* Local Smallmoney */ "smallmoney", 0)
insert into spt_datatype_info
/* ss_type, name, data_type,prec, scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (56, "int", 4, 10, 0, 10, null,null,null,null, 1, 0, 2, 0, 0, 0, /* Local Int */ "int", 0)
insert into spt_datatype_info /* sql server type is "intn" */
/* ss_type, name, data_type,prec,scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (38, "smallint tinyint",0, 0, 0, 10, 0, null,null,null, 1, 0, 2, 0, 0, 0, /* Local Intn */ "tinyint smallint", null)
insert into spt_datatype_info
/* ss_type, name, data_type,prec,scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (60, "money", 3, 19, 4, 10, null,"$", null,null, 1, 0, 2, 0, 1, 0, /* Local Money */ "money", 0)
insert into spt_datatype_info /* sql server type is "moneyn" */
/* ss_type, name, data_type,prec,scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (110,"smallmoney", 0, 0, 4, 10, 0, "$", null,null, 1, 0, 2, 0, 1, 0, /* Local Moneyn */ "smallmoneymoney", null)
insert into spt_datatype_info
/* ss_type, name, data_type,prec,scale,rdx, len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (52, "smallint", 5, 5, 0, 10, null,null,null,null, 1, 0, 2, 0, 0, 0, /* Local Smallint */ "smallint", 0)
insert into spt_datatype_info
/* ss_type,name, data_type,prec, scale,rdx, len, prf, suf,cp, nul,case, srch,unsigned,money,auto, local, aux */
values (35,"text",-1, 2147483647,null, null, 2147483647,"'", "'",null, 1, @case, 1, null, 0, null, /* Local Text */ "text", 0)
insert into spt_datatype_info
/* ss_type,name, data_type,prec,scale,rdx, len, prf, suf, cp, nul,case,srch,unsigned,money,auto, local, aux */
values (37,"varbinary",-3, null,null, null,null,"0x",null, "max length", 1, 0, 2, null, 0, null, /* Local Varbinary */ "varbinary", 0)
insert into spt_datatype_info
/* ss_type, name, data_type,prec, scale,rdx,len, prf, suf, cp, nul,case,srch,unsigned,money,auto,local, aux */
values (48, "tinyint",-6, 3, 0, 10, null,null,null, null, 1, 0, 2, 1, 0, 0, /* Local Tinyint */ "tinyint", 0)
insert into spt_datatype_info
/* ss_type,name, data_type,prec,scale,rdx, len, prf, suf, cp, nul,case, srch,unsigned,money,auto, local, aux */
values (39,"varchar",12, null,null, null,null,"'", "'", "max length", 1, @case, 3, null, 0, null, /* Local Varchar */ "varchar", 0)
insert into spt_datatype_info
/* ss_type,name, data_type,prec, scale,rdx, len, prf, suf, cp, nul,case,srch,unsigned,money,auto, local, aux */
values (34,"image",-4, 2147483647,null, null,2147483647,"0x",null,null,1, 0, 1, null, 0, null, /* Local Image */ "image", 0)
end
go
print "creating table spt_table_types"
go
create table spt_table_types (ss_type char(1),sag_type varchar(32))
go
insert into spt_table_types
values ('S','SYSTEM TABLE')
insert into spt_table_types
values ('U','TABLE')
insert into spt_table_types
values ('V','VIEW')
go
dump tran master with truncate_only
go
print "creating table spt_server_info"
go
create table spt_server_info (attribute_id int,
attribute_name varchar(60),
attribute_value varchar(255))
go
insert into spt_server_info
values (1,"DBMS_NAME","SQL Server")
insert into spt_server_info
values (2,"DBMS_VER",@@version)
insert into spt_server_info
values (6,"DBE_NAME","")
insert into spt_server_info
values (10,"OWNER_TERM","owner")
insert into spt_server_info
values (11,"TABLE_TERM","table")
insert into spt_server_info
values (12,"MAX_OWNER_NAME_LENGTH","30")
insert into spt_server_info
values (16,"IDENTIFIER_CASE","MIXED")
insert into spt_server_info
values (15,"COLUMN_LENGTH","30")
insert into spt_server_info
values (13,"TABLE_LENGTH","30")
insert into spt_server_info
values (100,"USERID_LENGTH","30")
insert into spt_server_info
values (17,"TX_ISOLATION","2")
insert into spt_server_info
values (18,"COLLATION_SEQ","")
insert into spt_server_info
values (14,"MAX_QUAL_LENGTH","30")
insert into spt_server_info
values (101,"QUALIFIER_TERM","database")
insert into spt_server_info
values (19,"SAVEPOINT_SUPPORT","Y")
insert into spt_server_info
values (20,"MULTI_RESULT_SETS","Y")
insert into spt_server_info
values (102,"NAMED_TRANSACTIONS","Y")
insert into spt_server_info
values (103,"SPROC_AS_LANGUAGE","Y")
insert into spt_server_info
values (103,"REMOTE_SPROC","Y")
insert into spt_server_info
values (22,"ACCESSIBLE_TABLES","Y")
insert into spt_server_info
values (104,"ACCESSIBLE_SPROC","Y")
insert into spt_server_info
values (105,"MAX_INDEX_COLS","16")
insert into spt_server_info
values (106,"RENAME_TABLE","Y")
insert into spt_server_info
values (107,"RENAME_COLUMN","Y")
insert into spt_server_info
values (108,"DROP_COLUMN","N")
insert into spt_server_info
values (109,"INCREASE_COLUMN_LENGTH","N")
insert into spt_server_info
values (110,"DDL_IN_TRANSACTION","N")
insert into spt_server_info
values (111,"DESCENDING_INDEXES","N")
insert into spt_server_info
values (112,"SP_RENAME","Y")
insert into spt_server_info
values (500,"SYS_SPROC_VERSION","01.01.2806")
go
grant select on spt_server_info to public
go
print "creating sp_tables"
go
create procedure sp_tables(@table_name varchar(32) = null,
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@table_type varchar(100) = null)
as
declare @type1 char(1),
@type2 char(1),
@type3 char(1),
@tableindex int
/* Special feature #1: enumerate databases when owner and name
are blank but qualifier is explicitly '%'. */
if @table_qualifier = '%' and
@table_owner = '' and
@table_name = ''
begin
select
table_qualifier = name,
table_owner=null,
table_name=null,
table_type = 'Database',
remarks = convert(varchar(254),null)
from master..sysdatabases
where name != 'model'
end
/* Special feature #2: enumerate owners when qualifier and name
are blank but owner is explicitly '%'. */
else if @table_qualifier = '' and
@table_owner = '%' and
@table_name = ''
begin
select distinct
table_qualifier = null,
table_owner=user_name(uid),
table_name=null,
table_type = 'Owner',
remarks = convert(varchar(254),null)
from sysobjects
end
else /* end of special features -- do normal processing */
begin
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
if @table_qualifier = ''
begin
/* in this case, we need to return an empty result set */
/* because the user has requested a database with an empty name */
select @table_name = ''
select @table_owner = ''
end else
begin
print "Table qualifier must be name of current database"
return
end
end
end
if @table_type is null
begin
select @type1 = 'U'
select @type2 = 'V'
select @type3 = 'S'
end
else
begin
if (charindex("'TABLE'",@table_type) != 0)
select @type1 = 'U'
else
select @type1 = 'X'
if (charindex("'SYSTEM TABLE'",@table_type) != 0)
select @type2 = 'S'
else
select @type2 = 'X'
if (charindex("'VIEW'",@table_type) != 0)
select @type3 = 'V'
else
select @type3 = 'X'
end
if @table_name is null
begin
select @table_name = '%'
end
else begin
if (@table_owner is null) and (charindex('%', @table_name) = 0)
begin
if exists (select * from sysobjects
where uid = user_id()
and name = @table_name
and (type = 'U' or type = 'V' or type = 'S'))
begin
select @table_owner = user_name()
end
end
end
if @table_owner is null
select @table_owner = '%'
select
table_qualifier = db_name(),
table_owner=user_name(o.uid),
table_name=o.name,
table_type = t.sag_type,
remarks = convert(varchar(254),null)
from
sysusers u,sysobjects o,master.dbo.spt_table_types t
where
o.name like @table_name
and user_name(o.uid) like @table_owner
and o.type = t.ss_type
and ( o.type = @type1 or o.type = @type2 or o.type = @type3)
and u.uid=user_id() /* constrain sysusers uid for use in subquery */
and (suser_id()=1 /* User is the System Administrator */
or o.uid=user_id() /* User created the object */
/* here's the magic... select the highest precedence of permissions in the order (user,group,public) */
or ((select max(((sign(uid)*abs(uid-16383))*2)+(protecttype&1))
from sysprotects p
/* outer join to correlate with all rows in sysobjects */
where p.id =* o.id
/* get rows for public,current user,user's group */
and (p.uid=0 or p.uid=user_id() or p.uid=*u.gid)
/* check for SELECT,EXECUTE privilege */
and (action in (193,224)))&1 /* more magic...normalize GRANT */
)=1 /* final magic...compare Grants */
)
order by table_type, table_qualifier, table_owner, table_name
end
go
grant execute on sp_tables to public
go
dump tran master with truncate_only
go
print "creating sp_statistics"
go
if (charindex('4.8', @@version) = 0
and charindex('4.9', @@version) = 0)
begin
print ""
print ""
print "Warning:"
print "you are installing the stored procedures "
print "on SQL Server with version less than 4.8, "
print "ignore the following error"
end
go
CREATE PROCEDURE sp_statistics (@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@index_name varchar(32) = '%',
@is_unique char(1) = 'N')
AS
DECLARE @indid int
DECLARE @lastindid int
DECLARE @table_id int
DECLARE @full_table_name char(70)
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
print "Table qualifier must be name of current database"
return
end
end
if @@trancount != 0
begin
raiserror 20001 "stored procedure sp_statistics can not be run while in a transaction"
return
end
create table #TmpIndex( table_qualifier varchar(32),
table_owner varchar(32),
table_name varchar(32),
index_qualifier varchar(32) null,
index_name varchar(32) null,
non_unique smallint null,
type smallint,
seq_in_index smallint null,
column_name varchar(32) null,
collation char(1) null,
remarks varchar(255) null,
index_id int null,
cardinality int null,
pages int null,
status smallint)
if @table_owner is null
begin
SELECT @full_table_name = @table_name
end
else
begin
SELECT @full_table_name = @table_owner + '.' + @table_name
end
SELECT @table_id = object_id(@full_table_name)
SELECT @indid=min(indid)
FROM sysindexes
WHERE id=@table_id
AND indid > 0
AND indid < 255
WHILE @indid != NULL
BEGIN
INSERT #TmpIndex
SELECT
DB_NAME(), /* table_qualifier */
USER_NAME(o.uid), /* table_owner */
o.name, /* table_name */
o.name, /* index_qualifier */
x.name, /* index_name */
0, /* non_unique */
1, /* type */
colid, /* seq_in_index */
INDEX_COL(@full_table_name,indid,colid), /* column_name */
"A", /* collation */
"", /* remarks */
@indid, /* index_id */
rowcnt(x.doampg), /* cardinality */
data_pgs(x.id,doampg), /* pages */
x.status /* status */
FROM sysindexes x,syscolumns c,sysobjects o
WHERE x.id=@table_id
AND x.id = o.id
AND x.id=c.id
AND c.colid<keycnt+ (x.status&16)/16
AND x.indid=@indid
/*
** Now move @indid to the next index.
*/
SELECT @lastindid = @indid
SELECT @indid = NULL
SELECT @indid = min(indid)
FROM sysindexes
WHERE id = @table_id
AND indid > @lastindid
AND indid < 255
END
UPDATE #TmpIndex
SET non_unique = 1
WHERE status&2 != 2
UPDATE #TmpIndex
SET type = 3, cardinality = NULL, pages = NULL
WHERE index_id > 1
/* now add row for table statistics */
INSERT #TmpIndex
SELECT
DB_NAME(), /* table_qualifier */
USER_NAME(o.uid), /* table_owner */
o.name, /* table_name */
null, /* index_qualifier */
null, /* index_name */
null, /* non_unique */
0, /* type */
null, /* seq_in_index */
null, /* column_name */
null, /* collation */
"", /* remarks */
0, /* index_id */
rowcnt(x.doampg), /* cardinality */
data_pgs(x.id,doampg), /* pages */
0 /* status */
FROM sysindexes x, sysobjects o
WHERE o.id=@table_id
AND x.id = o.id
AND (x.indid=0 or x.indid=1)
if @is_unique != 'Y'
SELECT
table_qualifier,
table_owner,
table_name,
non_unique,
index_qualifier,
index_name,
type,
seq_in_index,
column_name,
collation,
cardinality,
pages
FROM #TmpIndex
WHERE index_name like @index_name or index_name is null
ORDER BY non_unique,type,index_name,seq_in_index
else
SELECT
table_qualifier,
table_owner,
table_name,
non_unique,
index_qualifier,
index_name,
type,
seq_in_index,
column_name,
collation,
cardinality,
pages
FROM #TmpIndex
WHERE (non_unique = 0 or non_unique is NULL)
and (index_name like @index_name or index_name is null)
ORDER BY non_unique,type,index_name,seq_in_index
DROP TABLE #TmpIndex
go
if object_id('#TmpIndex') != null
drop table #TmpIndex
dump tran master with truncate_only
go
if (charindex('4.8', @@version) != 0
or charindex('4.9', @@version) != 0)
begin
print ""
print ""
print "Warning:"
print "you are installing the stored procedures "
print "on SQL Server with version 4.8 or greater. "
print "Ignore the following error."
end
go
CREATE PROCEDURE sp_statistics (@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@index_name varchar(32) = '%',
@is_unique char(1) = 'N')
AS
DECLARE @indid int
DECLARE @lastindid int
DECLARE @table_id int
DECLARE @full_table_name char(70)
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
print "Table qualifier must be name of current database"
return
end
end
if @@trancount != 0
begin
raiserror 20001 "stored procedure sp_statistics can not be run while in a transaction"
return
end
create table #TmpIndex( table_qualifier varchar(32),
table_owner varchar(32),
table_name varchar(32),
index_qualifier varchar(32) null,
index_name varchar(32) null,
non_unique smallint null,
type smallint,
seq_in_index smallint null,
column_name varchar(32) null,
collation char(1) null,
remarks varchar(255) null,
index_id int null,
cardinality int null,
pages int null,
status smallint)
if @table_owner is null
begin
SELECT @full_table_name = @table_name
end
else
begin
SELECT @full_table_name = @table_owner + '.' + @table_name
end
SELECT @table_id = object_id(@full_table_name)
SELECT @indid=min(indid)
FROM sysindexes
WHERE id=@table_id
AND indid > 0
AND indid < 255
WHILE @indid != NULL
BEGIN
INSERT #TmpIndex
SELECT
DB_NAME(), /* table_qualifier */
USER_NAME(o.uid), /* table_owner */
o.name, /* table_name */
o.name, /* index_qualifier */
x.name, /* index_name */
0, /* non_unique */
1, /* type */
colid, /* seq_in_index */
INDEX_COL(@full_table_name,indid,colid), /* column_name */
"A", /* collation */
"", /* remarks */
@indid, /* index_id */
x.rows, /* cardinality */
x.dpages, /* pages */
x.status /* status */
FROM sysindexes x,syscolumns c,sysobjects o
WHERE x.id=@table_id
AND x.id = o.id
AND x.id=c.id
AND c.colid<keycnt+ (x.status&16)/16
AND x.indid=@indid
/*
** Now move @indid to the next index.
*/
SELECT @lastindid = @indid
SELECT @indid = NULL
SELECT @indid = min(indid)
FROM sysindexes
WHERE id = @table_id
AND indid > @lastindid
AND indid < 255
END
UPDATE #TmpIndex
SET non_unique = 1
WHERE status&2 != 2
UPDATE #TmpIndex
SET type = 3, cardinality = NULL, pages = NULL
WHERE index_id > 1
/* now add row for table statistics */
INSERT #TmpIndex
SELECT
DB_NAME(), /* table_qualifier */
USER_NAME(o.uid), /* table_owner */
o.name, /* table_name */
null, /* index_qualifier */
null, /* index_name */
null, /* non_unique */
0, /* type */
null, /* seq_in_index */
null, /* column_name */
null, /* collation */
"", /* remarks */
0, /* index_id */
x.rows, /* cardinality */
x.dpages, /* pages */
0 /* status */
FROM sysindexes x, sysobjects o
WHERE o.id=@table_id
AND x.id = o.id
AND (x.indid=0 or x.indid=1)
if @is_unique != 'Y'
SELECT
table_qualifier,
table_owner,
table_name,
non_unique,
index_qualifier,
index_name,
type,
seq_in_index,
column_name,
collation,
cardinality,
pages
FROM #TmpIndex
WHERE index_name like @index_name or index_name is null
ORDER BY non_unique,type,index_name,seq_in_index
else
SELECT
table_qualifier,
table_owner,
table_name,
non_unique,
index_qualifier,
index_name,
type,
seq_in_index,
column_name,
collation,
cardinality,
pages
FROM #TmpIndex
WHERE (non_unique = 0 or non_unique is NULL)
and (index_name like @index_name or index_name is null)
ORDER BY non_unique,type,index_name,seq_in_index
DROP TABLE #TmpIndex
go
grant execute on sp_statistics to public
go
dump tran master with truncate_only
go
print "creating sp_columns"
go
/* this is the version for servers without support for UNION */
CREATE PROCEDURE sp_columns (@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@column_name varchar(32) = null )
AS
if @column_name is null
select @column_name = '%'
DECLARE @full_table_name char(70)
DECLARE @table_id int
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
print "Table qualifier must be name of current database"
return
end
end
if @table_name is null
begin
select @table_name='%'
end
if @table_owner is null
begin
SELECT @full_table_name = @table_name
end
else
begin
SELECT @full_table_name = @table_owner + '.' + @table_name
end
SELECT @table_id = 0
SELECT @table_id = object_id(@full_table_name)
if ((charindex('%',@full_table_name) = 0) and
(charindex('_',@full_table_name) = 0) and
@table_id != 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT
table_qualifier = DB_NAME(),
table_owner = USER_NAME(o.uid),
table_name = o.name,
column_name = c.name,
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.id=@table_id
AND c.id = o.id
AND c.type = d.ss_dtype
AND c.usertype *= t.usertype
AND c.name like @column_name
end else
begin
if @table_owner is null
select @table_owner = '%'
/* this block is for the case where there IS pattern
matching done on the table name */
SELECT
table_qualifier = DB_NAME(),
table_owner = USER_NAME(o.uid),
table_name = o.name,
column_name = c.name,
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type,
c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND c.id = o.id
AND c.usertype *= t.usertype
AND c.type = d.ss_dtype
AND o.type != 'P'
AND c.name like @column_name
ORDER BY table_qualifier, table_owner, table_name, colid
end
go
/* if this is a 4.2 or later SQL Server, then we want to delete the stored
procedure we just created, and create a new one which uses UNION
(this fixes some bugs involving UDT names and char NULL datatype
names)
*/
if (charindex('1.1', @@version) = 0
and charindex('4.0', @@version) = 0)
begin
drop proc sp_columns
dump tran master with truncate_only
end
else
begin
print ""
print ""
print "Installing catalog procedures on a 1.x or 4.0 server:"
print "Ignore the following error messages"
print ""
print ""
end
go
/* this is the version for servers which support UNION */
CREATE PROCEDURE sp_columns (@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@column_name varchar(32) = null )
AS
if @column_name is null
select @column_name = '%'
DECLARE @full_table_name char(70)
DECLARE @table_id int
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
print "Table qualifier must be name of current database"
return
end
end
if @table_name is null
begin
select @table_name='%'
end
if @table_owner is null
begin
SELECT @full_table_name = @table_name
end
else
begin
SELECT @full_table_name = @table_owner + '.' + @table_name
end
SELECT @table_id = 0
SELECT @table_id = object_id(@full_table_name)
if ((charindex('%',@full_table_name) = 0) and
(charindex('_',@full_table_name) = 0) and
@table_id != 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */
table_qualifier = DB_NAME(),
table_owner = USER_NAME(o.uid),
table_name = o.name,
column_name = c.name,
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type,
colid = c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.id=@table_id
AND c.id = o.id
AND c.type = d.ss_dtype
AND c.name like @column_name
AND d.ss_dtype IN (111, 109, 38, 110)
AND c.usertype < 100
UNION
SELECT /* All other types including user data types */
table_qualifier = DB_NAME(),
table_owner = USER_NAME(o.uid),
table_name = o.name,
column_name = c.name,
data_type=convert(smallint,
/* SS-Type 1 */
/* 33 3 3 4 44 5 5 2 5 55666*/
/* 45 7 9 5 78 0 2 2 6 89012*/
ascii(substring("8;<9<H<<<<<:<=6<5<A<?<@<GC?GD",
t.type%34+1,1))-60),
type_name=t.name,
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = c.name,
ss_data_type = c.type,
colid = c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.id=@table_id
AND c.id = o.id
AND c.type = d.ss_dtype
AND c.usertype *= t.usertype
AND c.name like @column_name
AND (d.ss_dtype NOT IN (111, 109, 38, 110)
OR c.usertype >= 100)
ORDER BY colid
end else
begin
if @table_owner is null
select @table_owner = '%'
/* this block is for the case where there IS pattern
matching done on the table name */
SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */
table_qualifier = DB_NAME(),
table_owner = USER_NAME(o.uid),
table_name = o.name,
column_name = c.name,
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type,
colid = c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND o.id = c.id
AND c.type = d.ss_dtype
AND o.type != 'P'
AND c.name like @column_name
AND d.ss_dtype IN (111, 109, 38, 110)
UNION
SELECT /* All other types including user data types */
table_qualifier = DB_NAME(),
table_owner = USER_NAME(o.uid),
table_name = o.name,
column_name = c.name,
data_type=convert(smallint,
/* SS-Type 1 */
/* 33 3 3 4 44 5 5 2 5 55666*/
/* 45 7 9 5 78 0 2 2 6 89012*/
ascii(substring("8;<9<H<<<<<:<=6<5<A<?<@<GC?GD",
t.type%34+1,1))-60),
type_name=t.name,
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks =convert(varchar(254),null),
ss_data_type = c.type,
colid = c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.name like @table_name
AND user_name(o.uid) like @table_owner
AND o.id = c.id
AND c.type = d.ss_dtype
AND c.usertype *= t.usertype
AND o.type != 'P'
AND c.name like @column_name
AND d.ss_dtype NOT IN (111, 109, 38, 110)
ORDER BY table_owner, table_name, colid
end
go
grant execute on sp_columns to public
go
dump tran master with truncate_only
go
print "creating sp_fkeys"
go
CREATE PROCEDURE sp_fkeys( @pktable_name varchar(32) = null,
@pktable_owner varchar(32) = null,
@pktable_qualifier varchar(32) = null,
@fktable_name varchar(32) = null,
@fktable_owner varchar(32) = null,
@fktable_qualifier varchar(32) = null )
as
declare @order_by_pk int
select @order_by_pk = 0
if (@pktable_name is null) and (@fktable_name is null)
begin
print "pk table name or fk table name must be given"
return
end
if @fktable_qualifier is not null
begin
if db_name() != @fktable_qualifier
begin
print "Foreign Key Table qualifier must be name of current database"
return
end
end
if @pktable_qualifier is not null
begin
if db_name() != @pktable_qualifier
begin
print "Primary Key Table qualifier must be name of current database"
return
end
end
if @pktable_name is null
begin
select @pktable_name = '%'
select @order_by_pk = 1
end
if @pktable_owner is null
select @pktable_owner = '%'
if @fktable_name is null
select @fktable_name = '%'
if @fktable_owner is null
select @fktable_owner = '%'
if @@trancount != 0
begin
raiserror 20001 "catalog procedure sp_fkeys can not be run in a transaction"
return
end
create table #fkeys( pktable_qualifier varchar(32),
pktable_owner varchar(32),
pktable_name varchar(32),
pkcolumn_name varchar(32),
fktable_qualifier varchar(32),
fktable_owner varchar(32),
fktable_name varchar(32),
fkcolumn_name varchar(32),
key_seq smallint)
insert into #fkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.depid),
object_name(k.depid),
( select name
from syscolumns
where id = k.depid
and colid = k.depkey1),
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
1
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 2
and c.colid = key1
if (@@rowcount = 0)
goto done
insert into #fkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.depid),
object_name(k.depid),
(select name
from syscolumns
where id = k.depid
and colid = k.depkey2),
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
2
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 2
and c.colid = key2
if (@@rowcount = 0)
goto done
insert into #fkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.depid),
object_name(k.depid),
(select name
from syscolumns
where id = k.depid
and colid = k.depkey3),
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
3
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 2
and c.colid = key3
if (@@rowcount = 0)
goto done
insert into #fkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.depid),
object_name(k.depid),
(select name
from syscolumns
where id = k.depid
and colid = k.depkey4),
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
4
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 2
and c.colid = key4
if (@@rowcount = 0)
goto done
insert into #fkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.depid),
object_name(k.depid),
(select name
from syscolumns
where id = k.depid
and colid = k.depkey5),
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
5
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 2
and c.colid = key5
if (@@rowcount = 0)
goto done
insert into #fkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.depid),
object_name(k.depid),
(select name
from syscolumns
where id = k.depid
and colid = k.depkey6),
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
6
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type =2
and c.colid = key6
if (@@rowcount = 0)
goto done
insert into #fkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.depid),
object_name(k.depid),
(select name
from syscolumns
where id = k.depid
and colid = k.depkey7),
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
7
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 2
and c.colid = key7
if (@@rowcount = 0)
goto done
insert into #fkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.depid),
object_name(k.depid),
(select name
from syscolumns
where id = k.depid
and colid = k.depkey8),
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
8
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 2
and c.colid = key8
done:
if @order_by_pk = 1
select
pktable_qualifier,
pktable_owner,
pktable_name,
pkcolumn_name,
fktable_qualifier,
fktable_owner,
fktable_name,
fkcolumn_name,
key_seq,
update_rule = convert(smallint, null),
delete_rule = convert(smallint,null)
from #fkeys
where fktable_name like @fktable_name
and fktable_owner like @fktable_owner
and pktable_name like @pktable_name
and pktable_owner like @pktable_owner
order by pktable_qualifier,pktable_owner,pktable_name, key_seq
else
select
pktable_qualifier,
pktable_owner,
pktable_name,
pkcolumn_name,
fktable_qualifier,
fktable_owner,
fktable_name,
fkcolumn_name,
key_seq,
update_rule = convert(smallint,null),
delete_rule = convert(smallint,null)
from #fkeys
where fktable_name like @fktable_name
and fktable_owner like @fktable_owner
and pktable_name like @pktable_name
and pktable_owner like @pktable_owner
order by fktable_qualifier,fktable_owner,fktable_name, key_seq
go
grant execute on sp_fkeys to public
go
dump tran master with truncate_only
go
print "creating sp_pkeys"
go
CREATE PROCEDURE sp_pkeys( @table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32)= null )
as
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
print "Table qualifier must be name of current database"
return
end
end
if @table_owner is null
select @table_owner = '%'
if @@trancount != 0
begin
raiserror 20001 "catalog procedure sp_pkeys can not be run in a transaction"
return
end
create table #pkeys( table_qualifier varchar(32),
table_owner varchar(32),
table_name varchar(32),
column_name varchar(32),
key_seq smallint,
update_delete_rule smallint null )
insert into #pkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
1,
null
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 1
and c.colid = k.key1
if (@@rowcount = 0)
goto done
insert into #pkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
2,
null
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 1
and c.colid = key2
if (@@rowcount = 0)
goto done
insert into #pkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
3,
null
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 1
and c.colid = key3
if (@@rowcount = 0)
goto done
insert into #pkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
4,
null
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 1
and c.colid = key4
if (@@rowcount = 0)
goto done
insert into #pkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
5,
null
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 1
and c.colid = key5
if (@@rowcount = 0)
goto done
insert into #pkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
6,
null
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 1
and c.colid = key6
if (@@rowcount = 0)
goto done
insert into #pkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
7,
null
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 1
and c.colid = key7
if (@@rowcount = 0)
goto done
insert into #pkeys
select
db_name(),
(select user_name(uid) from sysobjects o where o.id = k.id),
object_name(k.id),
c.name,
8,
null
from
syskeys k,syscolumns c
where
c.id = k.id
and k.type = 1
and c.colid = key8
done:
select
table_qualifier,
table_owner,
table_name,
column_name,
key_seq
from #pkeys
where table_name like @table_name
and table_owner like @table_owner
order by table_qualifier, table_owner, table_name,key_seq
go
grant execute on sp_pkeys to public
go
dump tran master with truncate_only
go
print "creating sp_stored_procedures"
go
create procedure sp_stored_procedures( @sp_name varchar(36) = null,
@sp_owner varchar(32) = null,
@sp_qualifier varchar(32) = null)
as
if @sp_qualifier is not null
begin
if db_name() != @sp_qualifier
begin
if @sp_qualifier = ''
begin
/* in this case, we need to return an empty result set */
/* because the user has requested a database with an empty name */
select @sp_name = ''
select @sp_owner = ''
end else
begin
print "Stored procedure qualifier must be name of current database"
return
end
end
end
if @sp_name is null
begin
select @sp_name = '%'
end
else begin
if (@sp_owner is null) and (charindex('%', @sp_name) = 0)
begin
if exists (select * from sysobjects
where uid = user_id()
and name = @sp_name
and type = 'P')
begin
select @sp_owner = user_name()
end
end
end
if @sp_owner is null
select @sp_owner = '%'
select
procedure_qualifier = db_name(),
procedure_owner = user_name(o.uid),
procedure_name = o.name +';'+ ltrim(str(p.number,5)),
num_input_params = -1,
num_output_params = -1,
num_result_sets = -1,
remarks = convert(varchar(254),null)
from
sysobjects o,sysprocedures p,sysusers u
where
o.name like @sp_name
and p.sequence = 0
and user_name(o.uid) like @sp_owner
and o.type = 'P'
and p.id = o.id
and u.uid=user_id() /* constrain sysusers uid for use in subquery */
and (suser_id()=1 /* User is the System Administrator */
or o.uid=user_id() /* User created the object */
/* here's the magic... select the highest precedence of permissions in the order (user,group,public) */
or ((select max(((sign(uid)*abs(uid-16383))*2)+(protecttype&1))
from sysprotects p
/* outer join to correlate with all rows in sysobjects */
where p.id =* o.id
/* get rows for public,current user,user's group */
and (p.uid=0 or p.uid=user_id() or p.uid=*u.gid)
/* check for SELECT,EXECUTE privilege */
and (action in (193,224)))&1 /* more magic...normalize GRANT */
)=1 /* final magic...compare Grants */
)
order by procedure_qualifier, procedure_owner, procedure_name
go
grant execute on sp_stored_procedures to public
go
dump tran master with truncate_only
go
print "creating sp_sproc_columns"
go
/* this is the version for servers without support for UNION */
CREATE PROCEDURE sp_sproc_columns (@procedure_name varchar(36) = '%',
@procedure_owner varchar(32) = null,
@procedure_qualifier varchar(32) = null,
@column_name varchar(32) = null )
AS
DECLARE @group_num int
DECLARE @semi_position int
if @column_name is null
select @column_name = '%'
DECLARE @full_procedure_name char(70)
DECLARE @procedure_id int
if @procedure_qualifier is not null
begin
if db_name() != @procedure_qualifier
begin
if @procedure_qualifier = ''
begin
/* in this case, we need to return an empty result set */
/* because the user has requested a database with an empty name */
select @procedure_name = ''
select @procedure_owner = ''
end else
begin
print "Procedure qualifier must be name of current database"
return
end
end
end
/* first we need to extract the procedure group number, if one exists */
select @semi_position = charindex(';',@procedure_name)
if (@semi_position > 0 )
begin
select @group_num = convert(int,substring(@procedure_name, @semi_position + 1, 2))
select @procedure_name = substring(@procedure_name, 1, @semi_position -1)
end
else
begin
select @group_num = 1
end
if @procedure_owner is null
begin
SELECT @full_procedure_name = @procedure_name
end
else
begin
SELECT @full_procedure_name = @procedure_owner + '.' + @procedure_name
end
SELECT @procedure_id = 0
SELECT @procedure_id = object_id(@full_procedure_name)
if ((charindex('%',@full_procedure_name) = 0) and
(charindex('_',@full_procedure_name) = 0) and
@procedure_id != 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT
procedure_qualifier = DB_NAME(),
procedure_owner = USER_NAME(o.uid),
procedure_name = o.name +';'+ ltrim(str(c.number,5)),
column_name = c.name,
column_type = convert(smallint, 0),
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.id=@procedure_id
AND c.id = o.id
AND c.type = d.ss_dtype
AND c.usertype *= t.usertype
AND c.name like @column_name
AND c.number = @group_num
end else
begin
if @procedure_owner is null
select @procedure_owner = '%'
/* this block is for the case where there IS pattern
matching done on the table name */
SELECT
procedure_qualifier = DB_NAME(),
procedure_owner = USER_NAME(o.uid),
procedure_name = o.name +';'+ ltrim(str(c.number,5)),
column_name = c.name,
column_type = convert(smallint, 0),
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type,
c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.name like @procedure_name
AND user_name(o.uid) like @procedure_owner
AND c.id = o.id
AND c.usertype *= t.usertype
AND c.type = d.ss_dtype
AND c.name like @column_name
AND o.type = 'P'
ORDER BY procedure_qualifier, procedure_owner, procedure_name, colid
end
go
/* if this is a 4.2 or later SQL Server, then we want to delete the stored
procedure we just created, and create a new one which uses UNION
(this fixes some bugs involving UDT names and char NULL datatype
names)
*/
if (charindex('1.1', @@version) = 0
and charindex('4.0', @@version) = 0)
begin
drop proc sp_sproc_columns
dump tran master with truncate_only
end
else
begin
print ""
print ""
print "Installing catalog procedures on a 1.x or 4.0 server:"
print "Ignore the following error messages"
print ""
print ""
end
go
/* this is the version for servers which support UNION */
CREATE PROCEDURE sp_sproc_columns (@procedure_name varchar(36) = '%',
@procedure_owner varchar(32) = null,
@procedure_qualifier varchar(32) = null,
@column_name varchar(32) = null )
AS
DECLARE @group_num int
DECLARE @semi_position int
if @column_name is null
select @column_name = '%'
DECLARE @full_procedure_name char(70)
DECLARE @procedure_id int
if @procedure_qualifier is not null
begin
if db_name() != @procedure_qualifier
begin
if @procedure_qualifier = ''
begin
/* in this case, we need to return an empty result set */
/* because the user has requested a database with an empty name */
select @procedure_name = ''
select @procedure_owner = ''
end else
begin
print "Procedure qualifier must be name of current database"
return
end
end
end
/* first we need to extract the procedure group number, if one exists */
select @semi_position = charindex(';',@procedure_name)
if (@semi_position > 0 )
begin
select @group_num = convert(int,substring(@procedure_name, @semi_position + 1, 2))
select @procedure_name = substring(@procedure_name, 1, @semi_position -1)
end
else
begin
select @group_num = 1
end
if @procedure_owner is null
begin
SELECT @full_procedure_name = @procedure_name
end
else
begin
SELECT @full_procedure_name = @procedure_owner + '.' + @procedure_name
end
SELECT @procedure_id = 0
SELECT @procedure_id = object_id(@full_procedure_name)
if ((charindex('%',@full_procedure_name) = 0) and
(charindex('_',@full_procedure_name) = 0) and
@procedure_id != 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */
SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */
procedure_qualifier = DB_NAME(),
procedure_owner = USER_NAME(o.uid),
procedure_name = o.name +';'+ ltrim(str(c.number,5)),
column_name = c.name,
column_type = convert(smallint, 0),
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type,
colid = c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t, sysprocedures p
WHERE
o.id=@procedure_id
AND c.id = o.id
AND c.type = d.ss_dtype
AND c.name like @column_name
AND d.ss_dtype IN (111, 109, 38, 110)
AND c.number = @group_num
UNION
SELECT /* All other types including user data types */
procedure_qualifier = DB_NAME(),
procedure_owner = USER_NAME(o.uid),
procedure_name = o.name +';'+ ltrim(str(c.number,5)),
column_name = c.name,
column_type = convert(smallint, 0),
data_type=convert(smallint,
/* SS-Type 1 */
/* 33 3 3 4 44 5 5 2 5 55666*/
/* 45 7 9 5 78 0 2 2 6 89012*/
ascii(substring("8;<9<H<<<<<:<=6<5<A<?<@<GC?GD",
t.type%34+1,1))-60),
type_name=t.name,
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type,
colid = c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.id=@procedure_id
AND c.id = o.id
AND c.type = d.ss_dtype
AND c.usertype *= t.usertype
AND c.name like @column_name
AND d.ss_dtype NOT IN (111, 109, 38, 110)
AND c.number = @group_num
ORDER BY colid
end else
begin
if @procedure_owner is null
select @procedure_owner = '%'
/* this block is for the case where there IS pattern
matching done on the table name */
SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */
procedure_qualifier = DB_NAME(),
procedure_owner = USER_NAME(o.uid),
procedure_name = o.name +';'+ ltrim(str(c.number,5)),
column_name = c.name,
column_type = convert(smallint, 0),
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type,
colid = c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.name like @procedure_name
AND user_name(o.uid) like @procedure_owner
AND o.id = c.id
AND c.type = d.ss_dtype
AND c.name like @column_name
AND o.type = 'P'
AND d.ss_dtype IN (111, 109, 38, 110)
UNION
SELECT /* All other types including user data types */
procedure_qualifier = DB_NAME(),
procedure_owner = USER_NAME(o.uid),
procedure_name = o.name +';'+ ltrim(str(c.number,5)),
column_name = c.name,
column_type = convert(smallint, 0),
data_type=convert(smallint,
/* SS-Type 1 */
/* 33 3 3 4 44 5 5 2 5 55666*/
/* 45 7 9 5 78 0 2 2 6 89012*/
ascii(substring("8;<9<H<<<<<:<=6<5<A<?<@<GC?GD",
t.type%34+1,1))-60),
type_name=t.name,
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60)),
radix=d.numeric_radix,
nullable=convert(smallint, convert(bit, c.status&8)),
remarks = convert(varchar(254),null),
ss_data_type = c.type,
colid = c.colid
FROM
syscolumns c,sysobjects o,master.dbo.spt_datatype_info d, systypes t
WHERE
o.name like @procedure_name
AND user_name(o.uid) like @procedure_owner
AND o.id = c.id
AND c.type = d.ss_dtype
AND c.usertype *= t.usertype
AND o.type = 'P'
AND c.name like @column_name
AND d.ss_dtype NOT IN (111, 109, 38, 110)
ORDER BY procedure_owner, procedure_name, colid
end
go
grant execute on sp_sproc_columns to public
go
print "creating sp_table_privileges"
go
CREATE PROCEDURE sp_table_privileges (
@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32)= null)
as
declare @table_id int,
@owner_id int,
@full_table_name char(70)
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
print "Table qualifier must be name of current database"
return
end
end
if @table_owner is null
begin
SELECT @full_table_name = @table_name
end
else
begin
SELECT @full_table_name = @table_owner + '.' + @table_name
end
SELECT @table_id = object_id(@full_table_name)
if @@trancount != 0
begin
raiserror 20001 "catalog procedure sp_table_privileges can not be run in a transaction"
return
end
create table #table_privileges
(table_qualifier varchar(32),
table_owner varchar(32),
table_name varchar(32),
grantor varchar(32),
grantee varchar(32),
select_privilege int,
insert_privilege int,
update_privilege int,
delete_privilege int,
is_grantable varchar(3),
uid int,
gid int)
insert into #table_privileges
select distinct db_name(),
user_name(o.uid),
o.name,
user_name(o.uid),
u.name,
0,
0,
0,
0,
'no',
u.uid,
u.gid
from sysusers u, sysobjects o
where o.id = @table_id
and u.uid != u.gid
/*
** now add row for table owner
*/
if exists (
select *
from #table_privileges
where grantor = grantee)
begin
update #table_privileges
set select_privilege = 1,
update_privilege = 1,
insert_privilege = 1,
delete_privilege = 1,
is_grantable = 'yes'
where grantor = grantee
end
else
begin
insert into #table_privileges
select db_name(),
user_name(o.uid),
o.name,
user_name(o.uid),
user_name(o.uid),
1,
1,
1,
1,
'yes',
o.uid,
u.gid
from sysobjects o, sysusers u
where o.id = @table_id
and u.uid = o.uid
end
update #table_privileges
set select_privilege = 1
where exists (select * from sysprotects
where id = @table_id
and (#table_privileges.uid = uid
or #table_privileges.gid = uid
or uid = 0)
and protecttype = 205
and action = 193)
and not exists (select * from sysprotects
where id = @table_id
and (#table_privileges.uid = uid
or #table_privileges.gid = uid
or uid = 0)
and protecttype = 206
and action = 193)
update #table_privileges
set insert_privilege = 1
where exists (select * from sysprotects
where id = @table_id
and (#table_privileges.uid = uid
or #table_privileges.gid = uid
or uid = 0)
and protecttype = 205
and action = 195)
and not exists (select * from sysprotects
where id = @table_id
and (#table_privileges.uid = uid
or #table_privileges.gid = uid
or uid = 0)
and protecttype = 206
and action = 195)
update #table_privileges
set delete_privilege = 1
where exists (select * from sysprotects
where id = @table_id
and (#table_privileges.uid = uid
or #table_privileges.gid = uid
or uid = 0)
and protecttype = 205
and action = 196)
and not exists (select * from sysprotects
where id = @table_id
and (#table_privileges.uid = uid
or #table_privileges.gid = uid
or uid = 0)
and protecttype = 206
and action = 196)
update #table_privileges
set update_privilege = 1
where exists (select * from sysprotects
where id = @table_id
and (#table_privileges.uid = uid
or #table_privileges.gid = uid
or uid = 0)
and protecttype = 205
and action = 197)
and not exists (select * from sysprotects
where id = @table_id
and (#table_privileges.uid = uid
or #table_privileges.gid = uid
or uid = 0)
and protecttype = 206
and action = 197)
create table #table_priv2
(table_qualifier varchar(32),
table_owner varchar(32),
table_name varchar(32),
grantor varchar(32),
grantee varchar(32),
privilege varchar(32),
is_grantable varchar(3))
insert into #table_priv2
select
table_qualifier,
table_owner,
table_name,
grantor,
grantee,
'SELECT',
is_grantable
from #table_privileges
where select_privilege = 1
insert into #table_priv2
select
table_qualifier,
table_owner,
table_name,
grantor,
grantee,
'INSERT',
is_grantable
from #table_privileges
where insert_privilege = 1
insert into #table_priv2
select
table_qualifier,
table_owner,
table_name,
grantor,
grantee,
'DELETE',
is_grantable
from #table_privileges
where delete_privilege = 1
insert into #table_priv2
select
table_qualifier,
table_owner,
table_name,
grantor,
grantee,
'UPDATE',
is_grantable
from #table_privileges
where update_privilege = 1
select * from #table_priv2
order by privilege
go
grant execute on sp_table_privileges to public
go
dump tran master with truncate_only
go
print "creating sp_column_privileges"
go
CREATE PROCEDURE sp_column_privileges (
@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32)= null,
@column_name varchar(32) = null)
as
declare @table_id int,
@owner_id int
DECLARE @full_table_name char(70)
declare @low int /* range of userids to check */
declare @high int
declare @objid int /* id of @name if object */
declare @owner_name varchar(32)
select @low = 0, @high = 32767
if @column_name is null
select @column_name = '%'
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
print "Table qualifier must be name of current database"
return
end
end
if @table_owner is null
begin
SELECT @full_table_name = @table_name
end
else
begin
SELECT @full_table_name = @table_owner + '.' + @table_name
end
select @table_id = object_id(@full_table_name)
if @@trancount != 0
begin
raiserror 20001 "catalog procedure sp_column_privileges can not be run in a transaction"
return
end
/*
** We need to create a table which will contain a row for every row to
** be returned to the client.
*/
create table #column_privileges
(table_qualifier varchar(32),
table_owner varchar(32),
table_name varchar(32),
column_name varchar(32),
grantor varchar(32),
grantee varchar(32),
select_privilege int,
select_grantable int,
insert_privilege int,
insert_grantable int,
update_privilege int,
update_grantable int,
delete_privilege int,
delete_grantable int,
references_privilege int null,
references_grantable int null,
uid int,
gid int,
is_grantable varchar(3))
/*
** insert a row for the table owner (who has all permissions)
*/
select @owner_name = (select user_name(uid)
from sysobjects
where id = @table_id)
insert into #column_privileges
select
db_name(),
@owner_name,
@table_name,
name,
@owner_name,
@owner_name,
1,
1,
1,
1,
1,
1,
1,
1,
null,
null,
user_id(@owner_name),
0,
'yes'
from syscolumns
where id = @table_id
/*
** now stick a row in the table for every user in the database
** we will need to weed out those who have no permissions later
** (and yes this is a cartesion product: the uid field in sysprotects
** can also have a group id, in which case we need to extend those
** privileges to all group members).
*/
insert into #column_privileges
select distinct
db_name(),
user_name(o.uid),
@table_name,
c.name,
user_name(o.uid),
u.name,
0,
0,
0,
0,
0,
0,
0,
0,
null,
null,
u.uid,
u.gid,
'no'
from sysusers u, syscolumns c, sysobjects o
where o.id = @table_id
and c.id = o.id
and u.gid != u.uid
and u.name != @owner_name
/*
** we need to create another temporary table to contain all the various
** protection information for the table in question
*/
create table #protects (uid smallint,
action tinyint,
protecttype tinyint,
name varchar(32))
insert into #protects
select p.uid,
p.action,
p.protecttype,
isnull(col_name(id, c.number), "All")
from sysprotects p, master.dbo.spt_values c,
master.dbo.spt_values a, master.dbo.spt_values b
where convert(tinyint, substring(isnull(p.columns, 0x1), c.low, 1))
& c.high != 0
and c.number <=
(select count(*)
from syscolumns
where id = @table_id)
and a.type = "T"
and a.number = p.action
and b.type = "T"
and b.number = p.protecttype
and p.id = @table_id
and p.uid between @low and @high
update #column_privileges
set select_privilege = 1
where exists
(select * from #protects
where protecttype = 205
and action = 193
and ( name = #column_privileges.column_name
or name = 'All')
and ( uid = 0
or uid = #column_privileges.gid
or uid = #column_privileges.uid))
and not exists (select * from #protects
where protecttype = 206
and action = 193
and ( name = #column_privileges.column_name
or name = 'All')
and ( uid = 0
or uid = #column_privileges.gid
or uid = #column_privileges.uid))
update #column_privileges
set insert_privilege = 1
where exists (select * from #protects
where protecttype = 205
and action = 195
and ( name = #column_privileges.column_name
or name = 'All')
and ( uid = 0
or uid = #column_privileges.gid
or uid = #column_privileges.uid))
and not exists (select * from #protects
where protecttype = 206
and action = 195
and ( name = #column_privileges.column_name
or name = 'All')
and ( uid = 0
or uid = #column_privileges.gid
or uid = #column_privileges.uid))
update #column_privileges
set insert_privilege = 1
where exists (select * from #protects
where protecttype = 205
and action = 196
and ( name = #column_privileges.column_name
or name = 'All')
and ( uid = 0
or uid = #column_privileges.gid
or uid = #column_privileges.uid))
and not exists (select * from #protects
where protecttype = 206
and action = 196
and ( name = #column_privileges.column_name
or name = 'All')
and ( uid = 0
or uid = #column_privileges.gid
or uid = #column_privileges.uid))
update #column_privileges
set update_privilege = 1
where exists (select * from #protects
where protecttype = 205
and action = 197
and ( name = #column_privileges.column_name
or name = 'All')
and ( uid = 0
or uid = #column_privileges.gid
or uid = #column_privileges.uid))
and not exists (select * from #protects
where protecttype = 206
and action = 197
and ( name = #column_privileges.column_name
or name = 'All')
and ( uid = 0
or uid = #column_privileges.gid
or uid = #column_privileges.uid))
create table #column_priv2
(table_qualifier varchar(32),
table_owner varchar(32),
table_name varchar(32),
column_name varchar(32),
grantor varchar(32),
grantee varchar(32),
privilege varchar(32),
is_grantable varchar(3))
insert into #column_priv2
select
table_qualifier,
table_owner,
table_name,
column_name,
grantor,
grantee,
'SELECT',
is_grantable
from #column_privileges
where select_privilege = 1
insert into #column_priv2
select
table_qualifier,
table_owner,
table_name,
column_name,
grantor,
grantee,
'INSERT',
is_grantable
from #column_privileges
where insert_privilege = 1
insert into #column_priv2
select
table_qualifier,
table_owner,
table_name,
column_name,
grantor,
grantee,
'UPDATE',
is_grantable
from #column_privileges
where update_privilege = 1
select * from #column_priv2
where column_name like @column_name
order by column_name, privilege
go
grant execute on sp_column_privileges to public
go
dump tran master with truncate_only
go
print "creating sp_server_info"
if (exists (select * from sysobjects where name = 'sp_server_info'))
begin
drop proc sp_server_info
end
go
create proc sp_server_info (@attribute_id int = null)
as
if @attribute_id is not null
select *
from master.dbo.spt_server_info
where attribute_id = @attribute_id
else
select *
from master.dbo.spt_server_info
go
grant execute on sp_server_info to public
go
print "creating sp_datatype_info"
if (exists (select * from sysobjects where name = 'sp_datatype_info'))
begin
drop proc sp_datatype_info
end
go
/* the messiness of 'data_type' was to get around the problem of
returning the correct lengths for user defined types. the join
on the type name ensures all user defined types are returned, but
this puts a null in the data_type column. by forcing an embedded
select and correlating it with the current row in systypes, we get
the correct data_type mapping even for user defined types (kwg) */
/* this is the version for servers without support for UNION */
create proc sp_datatype_info (@data_type int = 0)
as
if @data_type = 0
select
type_name = t.name,
d.data_type,
precision=isnull(d.data_precision, convert(int,t.length)),
d.literal_prefix,
d.literal_suffix,
e.create_params,
d.nullable,
d.case_sensitive,
d.searchable,
d.unsigned_attribute,
d.money,
d.auto_increment,
d.local_type_name
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
where d.ss_dtype = t.type and
t.usertype *= e.user_type and
t.type not in (111,109,38,110) /* get rid of nullable types */
order by d.data_type, type_name
else
select
type_name = t.name,
d.data_type,
precision=isnull(d.data_precision, convert(int,t.length)),
d.literal_prefix,
d.literal_suffix,
e.create_params,
d.nullable,
d.case_sensitive,
d.searchable,
d.unsigned_attribute,
d.money,
d.auto_increment,
d.local_type_name
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
where data_type = @data_type and
d.ss_dtype = t.type and
t.usertype *= e.user_type and
t.type not in (111,109,38,110)
order by type_name
go
/* if this is a 4.2 or later SQL Server, then we want to delete the stored
procedure we just created, and create a new one which uses UNION
(this fixes some bugs involving UDT names)
*/
if (charindex('1.1', @@version) = 0
and charindex('4.0', @@version) = 0)
begin
drop proc sp_datatype_info
dump tran master with truncate_only
end
else
begin
print ""
print ""
print "Installing catalog procedures on a 1.x or 4.0 server:"
print "Ignore the following error messages"
print ""
print ""
end
go
/* this is the version for servers which support UNION */
create proc sp_datatype_info (@data_type int = 0)
as
if @data_type = 0
select /* Real SQL Server data types */
type_name = t.name,
d.data_type,
precision=isnull(d.data_precision, convert(int,t.length)),
d.literal_prefix,
d.literal_suffix,
e.create_params,
d.nullable,
d.case_sensitive,
d.searchable,
d.unsigned_attribute,
d.money,
d.auto_increment,
d.local_type_name
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
where d.ss_dtype = t.type and
t.usertype *= e.user_type and
t.usertype not in (80,18) and
t.usertype < 100 and
t.type not in (111,109,38,110) /* get rid of nullable types */
and t.name != 'nchar' /* get rid of bogus 4.9.1 types */
and t.name != 'nvarchar' /* get rid of more bogus 4.9.1 types */
UNION
select /* SQL Server SYSNAME, TIMESTAMP and user data types */
type_name = t.name,
d.data_type,
precision=isnull(d.data_precision, convert(int,t.length)),
d.literal_prefix,
d.literal_suffix,
e.create_params,
d.nullable,
d.case_sensitive,
d.searchable,
d.unsigned_attribute,
d.money,
d.auto_increment,
t.name
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
where d.ss_dtype = t.type and
t.usertype *= e.user_type and
(t.usertype in (80,18) or
t.usertype >= 100) and
t.type not in (111,109,38,110) /* get rid of nullable types */
order by d.data_type, type_name
else
select /* Real SQL Server data types */
type_name = t.name,
d.data_type,
precision=isnull(d.data_precision, convert(int,t.length)),
d.literal_prefix,
d.literal_suffix,
e.create_params,
d.nullable,
d.case_sensitive,
d.searchable,
d.unsigned_attribute,
d.money,
d.auto_increment,
d.local_type_name
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
where data_type = @data_type and
d.ss_dtype = t.type and
t.usertype *= e.user_type and
t.usertype not in (80,18) and
t.usertype < 100 and
t.type not in (111,109,38,110)
UNION
select /* SQL Server SYSNAME, TIMESTAMP and user data types */
type_name = t.name,
d.data_type,
precision=isnull(d.data_precision, convert(int,t.length)),
d.literal_prefix,
d.literal_suffix,
e.create_params,
d.nullable,
d.case_sensitive,
d.searchable,
d.unsigned_attribute,
d.money,
d.auto_increment,
t.name
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t
where data_type = @data_type and
d.ss_dtype = t.type and
t.usertype *= e.user_type and
(t.usertype in (80,18) or
t.usertype >= 100) and
t.type not in (111,109,38,110)
order by type_name
go
grant execute on sp_datatype_info to public
go
dump tran master with truncate_only
go
print "creating sp_special_columns"
if (exists (select * from sysobjects where name = 'sp_special_columns'))
begin
drop proc sp_special_columns
end
go
dump tran master with truncate_only
go
CREATE PROCEDURE sp_special_columns (@table_name varchar(32),
@table_owner varchar(32) = null,
@table_qualifier varchar(32) = null,
@col_type char(1) = 'R')
AS
DECLARE @indid int
DECLARE @table_id int
DECLARE @full_table_name char(70)
DECLARE @msg char(70)
if @table_qualifier is not null
begin
if db_name() != @table_qualifier
begin
print "Table qualifier must be name of current database"
return
end
end
if @table_owner is null
begin
SELECT @full_table_name = @table_name
end
else
begin
SELECT @full_table_name = @table_owner + '.' + @table_name
end
SELECT @table_id = object_id(@full_table_name)
/* if we just need ROWVER, just run that query */
if @col_type = 'V'
BEGIN
SELECT
scope = convert(smallint, 0),
column_name = c.name,
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c.length,1))
-60)),
type_name=t.name,
precision=isnull(d.data_precision, convert(int,c.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c.length,1))-60)),
length=isnull(d.length, convert(int,c.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c.length,
1))-60))
FROM
systypes t,syscolumns c, master.dbo.spt_datatype_info d
WHERE
c.id=@table_id
AND c.type = d.ss_dtype
AND c.usertype = 80
AND t.usertype = 80
RETURN
END
/* now find the id of the 'best' index for this table */
SELECT @indid = (
SELECT MIN(indid)
FROM sysindexes
WHERE status&2 = 2
AND id = @table_id
AND indid > 0)
SELECT
scope = convert(smallint, 0),
column_name = INDEX_COL(@full_table_name,indid,c.colid),
data_type=d.data_type+convert(smallint,
isnull(d.aux,
ascii(substring("666AAA@@@CB??GG",
2*(d.ss_dtype%35+1)+2-8/c2.length,1))
-60)),
type_name=rtrim(substring(d.type_name,
1+isnull(d.aux,
ascii(substring("III<<<MMMI<<A<A",
2*(d.ss_dtype%35+1)+2-8/c2.length,
1))-60), 13)),
precision=isnull(d.data_precision, convert(int,c2.length))
+isnull(d.aux, convert(int,
ascii(substring("???AAAFFFCKFOLS",
2*(d.ss_dtype%35+1)+2-8/c2.length,1))-60)),
length=isnull(d.length, convert(int,c2.length)) +convert(int,
isnull(d.aux,
ascii(substring("AAA<BB<DDDHJSPP",
2*(d.ss_dtype%35+1)+2-8/c2.length,
1))-64)),
scale = d.numeric_scale +convert(smallint,
isnull(d.aux,
ascii(substring("<<<<<<<<<<<<<<?",
2*(d.ss_dtype%35+1)+2-8/c2.length,
1))-60))
FROM sysindexes x,syscolumns c, master.dbo.spt_datatype_info d, systypes t, syscolumns c2
WHERE x.id=@table_id
AND c2.name = INDEX_COL(@full_table_name,@indid,c.colid)
AND c2.id =x.id
AND c.id = x.id
AND c.colid<keycnt+ (x.status&16)/16
AND x.indid=@indid
AND c2.type = d.ss_dtype
AND c2.usertype *= t.usertype
go
grant execute on sp_special_columns to public
go
print "creating sp_databases"
if (exists (select * from sysobjects where name = 'sp_databases'))
begin
drop proc sp_databases
end
go
create procedure sp_databases
as
create table #databases ( database_name varchar(32),
size int)
insert into #databases
select name,
(select sum(size) from master.dbo.sysusages
where dbid = d.dbid )
from master.dbo.sysdatabases d
select database_name, database_size = size*2,
remarks = convert(varchar(254),null) from #databases
go
grant execute on sp_databases to public
go
dump tran master with truncate_only
go
/*******************************************************************************/
/* This portion returns everything back to normal */
/*******************************************************************************/
use master
go
if exists (select * from sysobjects
where name = 'sp_configure' and sysstat & 7 = 4)
begin
execute sp_configure 'update',0
end
reconfigure with override
go
checkpoint
go