解決從 64 位元 SQL Server 用戶端對連結 32 位元 SQL Server 伺服器執行分散式查詢時,可能會收到錯誤訊息
This file has been truncated, but you can view the full file.
/* | |
** INSTCAT.SQL | |
** Installs catalog stored procedures on the Microsoft SQL Server. | |
** Copyright Microsoft, Inc. 1994 - 2000 | |
** All Rights Reserved. | |
** | |
** Owners: | |
** | |
** <owner current="true" primary="true">iliak</owner> | |
*/ | |
/* | |
INTERNAL NOTE: | |
When this file is updated, you MUST change the last row inserted into | |
spt_server_info to be version number of this file. the convention is | |
j.nn.bbb, where j is the major version number ('7' now), nn is the minor | |
version number ('10' now), and bbb is the build number. | |
insert into spt_server_info | |
values (500, 'SYS_SPROC_VERSION', '8.00.bbb') | |
*/ | |
/****************************************************************************/ | |
/* This portion sets up the ability to perform all the functions in this */ | |
/* script */ | |
/****************************************************************************/ | |
go | |
use master | |
go | |
dump tran master with no_log | |
go | |
set quoted_identifier on | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_configure' and type = 'P ')) | |
begin | |
execute sp_configure 'update',1 | |
end | |
reconfigure with override | |
go | |
exec sp_MS_upd_sysobj_category 1 /*Capture datetime for use below.*/ | |
go | |
/* | |
** If old versions of tables exist, drop them. | |
*/ | |
if (exists (select * from sysobjects | |
where name = 'MSdatatype_info' and type = 'U ')) | |
drop table MSdatatype_info | |
go | |
if (exists (select * from sysobjects | |
where name = 'MSdatatype_info_ext' and type = 'U ')) | |
drop table MSdatatype_info_ext | |
go | |
if (exists (select * from sysobjects | |
where name = 'MStable_types' and type = 'U ')) | |
drop table MStable_types | |
go | |
if (exists (select * from sysobjects | |
where name = 'MSserver_info' and type = 'U ')) | |
drop table MSserver_info | |
go | |
if (exists (select * from sysobjects | |
where name = 'spt_table_types' and type = 'U ')) | |
drop table spt_table_types | |
go | |
/* | |
** If tables or procs already exist, drop them. | |
*/ | |
if (exists (select * from sysobjects | |
where name = 'spt_datatype_info' and type = 'U ')) | |
drop table spt_datatype_info | |
go | |
if (exists (select * from sysobjects | |
where name = 'spt_datatype_info_ext' and type = 'U ')) | |
drop table spt_datatype_info_ext | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_add_server_sortinfo' and type = 'P ')) | |
drop proc sp_add_server_sortinfo | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_add_server_sortinfo75' and type = 'P ')) | |
drop proc sp_add_server_sortinfo75 | |
go | |
if (exists (select * from sysobjects | |
where name = 'spt_server_info' and type = 'U ')) | |
drop table spt_server_info | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_tables' and type = 'P ')) | |
drop proc sp_tables | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_statistics' and type = 'P ')) | |
drop proc sp_statistics | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_columns' and type = 'P ')) | |
drop proc sp_columns | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_fkeys' and type = 'P ')) | |
drop proc sp_fkeys | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_pkeys' and type = 'P ')) | |
drop proc sp_pkeys | |
dump tran master with no_log | |
go | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_stored_procedures' and type = 'P ')) | |
drop proc sp_stored_procedures | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_sproc_columns' and type = 'P ')) | |
drop proc sp_sproc_columns | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_table_privileges' and type = 'P ')) | |
drop proc sp_table_privileges | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_column_privileges' and type = 'P ')) | |
drop proc sp_column_privileges | |
go | |
dump tran master with no_log | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_server_info' and type = 'P ')) | |
drop proc sp_server_info | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_datatype_info' and type = 'P ')) | |
drop proc sp_datatype_info | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_special_columns' and type = 'P ')) | |
drop proc sp_special_columns | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_databases' and type = 'P ')) | |
drop proc sp_databases | |
go | |
dump tran master with no_log | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_ddopen' and type = 'P ')) | |
drop proc sp_ddopen | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_tableswc' and type = 'P ')) | |
drop proc sp_tableswc | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_tablecollations' and type = 'P')) | |
drop proc sp_tablecollations | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_bcp_dbcmptlevel' and type = 'P')) | |
drop proc sp_bcp_dbcmptlevel | |
go | |
dump tran master with no_log | |
go | |
if (exists (select * from sysobjects | |
where name = 'spt_provider_types' and type = 'U ')) | |
begin | |
drop table spt_provider_types | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_catalogs_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_catalogs_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_column_privileges_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_column_privileges_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_columns_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_columns_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_check_constraints_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_check_constraints_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_check_constbytable_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_check_constbytable_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_foreign_keys_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_foreign_keys_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_indexes_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_indexes_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_primary_keys_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_primary_keys_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_procedure_params_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_procedure_params_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_procedures_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_procedures_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_provider_types_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_provider_types_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_schemata_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_schemata_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_statistics_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_statistics_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_tables_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_tables_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_tables_info_rowset' and type = 'P ')) | |
begin | |
drop procedure sp_tables_info_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_tables_info_rowset_64' and type = 'P ')) | |
begin | |
drop procedure sp_tables_info_rowset_64 | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_table_constraints_rowset' and type = 'P ')) | |
begin | |
drop proc sp_table_constraints_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_table_privileges_rowset' and type = 'P ')) | |
begin | |
drop proc sp_table_privileges_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_linkedservers_rowset' and type = 'P ')) | |
begin | |
drop proc sp_linkedservers_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_table_statistics_rowset' and type = 'P ')) | |
begin | |
drop proc sp_table_statistics_rowset | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_oledb_column_constraints' and type = 'P ')) | |
begin | |
drop proc sp_oledb_column_constraints | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_oledb_indexinfo' and type = 'P ')) | |
begin | |
drop proc sp_oledb_indexinfo | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_oledb_ro_usrname' and type = 'P ')) | |
begin | |
drop proc sp_oledb_ro_usrname | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_oledb_deflang' and type = 'P ')) | |
begin | |
drop proc sp_oledb_deflang | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_oledb_defdb' and type = 'P ')) | |
begin | |
drop proc sp_oledb_defdb | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_oledb_database' and type = 'P ')) | |
begin | |
drop proc sp_oledb_database | |
dump tran master with no_log | |
end | |
go | |
if (exists (select * from sysobjects | |
where name = 'sp_oledb_language' and type = 'P ')) | |
begin | |
drop proc sp_oledb_language | |
dump tran master with no_log | |
end | |
go | |
print 'creating table spt_datatype_info_ext' | |
go | |
if (charindex('6.00', @@version) = 0 and | |
charindex('6.50', @@version) = 0 and | |
charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin /* Pre 6.0 server */ | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 6.0 SQL Server.' | |
print 'Ignore the following error.' | |
create table spt_datatype_info_ext ( | |
user_type smallint not null, | |
CREATE_PARAMS varchar(32) null, | |
AUTO_INCREMENT smallint null, | |
typename varchar(32)) | |
end | |
go | |
if (charindex('6.00', @@version) > 0 or | |
charindex('6.50', @@version) > 0 or | |
charindex('7.00', @@version) > 0 or | |
charindex('8.00', @@version) > 0) | |
begin /* 6.0 or later server */ | |
create table spt_datatype_info_ext ( | |
user_type smallint not null, | |
CREATE_PARAMS varchar(32) null, | |
AUTO_INCREMENT smallint null, | |
typename sysname) /* from systypes, to avoid xusertype hard-code */ | |
end | |
go | |
grant select on spt_datatype_info_ext to public | |
go | |
insert into spt_datatype_info_ext | |
/* CHAR user_type, create_params, auto_increment */ | |
values (1, 'length' ,0, 'char') | |
insert into spt_datatype_info_ext | |
/* VARCHAR user_type, create_params, auto_increment */ | |
values (2, 'max length' ,0, 'varchar') | |
insert into spt_datatype_info_ext | |
/* BINARY user_type, create_params, auto_increment */ | |
values (3, 'length' ,0, 'binary') | |
insert into spt_datatype_info_ext | |
/* VARBINARY user_type, create_params, auto_increment */ | |
values (4, 'max length' ,0, 'varbinary') | |
if (charindex('6.00', @@version) > 0 or | |
charindex('6.50', @@version) > 0 or | |
charindex('7.00', @@version) > 0 or | |
charindex('8.00', @@version) > 0) | |
begin /* Add 6.0 data types */ | |
insert into spt_datatype_info_ext | |
/* DECIMAL user_type, create_params, auto_increment */ | |
values (26, 'precision,scale' ,0, 'decimal') | |
insert into spt_datatype_info_ext | |
/* NUMERIC user_type, create_params, auto_increment */ | |
values (25, 'precision,scale' ,0, 'numeric') | |
insert into spt_datatype_info_ext | |
/* DECIMAL IDENTITY user_type, create_params, auto_increment */ | |
values (26, 'precision' ,1, 'decimal') | |
insert into spt_datatype_info_ext | |
/* NUMERIC IDENTITY user_type, create_params, auto_increment */ | |
values (25, 'precision' ,1, 'numeric') | |
end | |
else /* Pre 6.0 server, add SYSNAME create param */ | |
begin | |
insert into spt_datatype_info_ext | |
/* SYSNAME user_type, create_param, auto_increments */ | |
values (18, 'max length' ,0, 'sysname') | |
end | |
go | |
if (charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 8.0 SQL Server.' | |
print 'Ignore the following errors.' | |
end | |
go | |
if (charindex('7.00', @@version) > 0 or | |
charindex('8.00', @@version) > 0) | |
begin /* Update usertypes for 8.0 server */ | |
begin tran | |
insert into spt_datatype_info_ext | |
/* NCHAR user_type, create_params, auto_increment */ | |
values (0, 'length' ,0, 'nchar') | |
insert into spt_datatype_info_ext | |
/* NVARCHAR user_type, create_params, auto_increment */ | |
values (0, 'max length' ,0, 'nvarchar') | |
/* SET user_type TO SPHINX VALUES */ | |
update spt_datatype_info_ext set user_type = xusertype | |
from spt_datatype_info_ext e, systypes t where t.name = e.typename | |
commit tran | |
end | |
go | |
create unique clustered index datatypeinfoextclust on spt_datatype_info_ext(user_type,AUTO_INCREMENT) | |
go | |
print 'creating table spt_datatype_info' | |
go | |
if (charindex('6.00', @@version) = 0 and | |
charindex('6.50', @@version) = 0 and | |
charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin /* Pre 6.0 server */ | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 6.0 SQL Server.' | |
print 'Ignore the following error.' | |
create table spt_datatype_info ( | |
ss_dtype tinyint not null, | |
fixlen int null, /* datatype len for variable, else null */ | |
ODBCVer tinyint null, /* version if needed, else null */ | |
TYPE_NAME varchar(32) not null, | |
DATA_TYPE smallint not null, | |
data_precision int null, | |
numeric_scale smallint null, /* min scale if 6.0 */ | |
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(32) null, | |
charbin tinyint null, /* 0 for char/binary types, NULL for all others */ | |
SQL_DATA_TYPE smallint not null, | |
SQL_DATETIME_SUB smallint null) | |
end | |
go | |
if (charindex('6.00', @@version) > 0 or | |
charindex('6.50', @@version) > 0 or | |
charindex('7.00', @@version) > 0 or | |
charindex('8.00', @@version) > 0) | |
begin /* 6.0 or later server */ | |
create table spt_datatype_info ( | |
ss_dtype tinyint not null, | |
fixlen int null, /* datatype len for variable, else null */ | |
ODBCVer tinyint null, /* version if needed, else null */ | |
TYPE_NAME sysname not null, | |
DATA_TYPE smallint not null, | |
data_precision int null, | |
numeric_scale smallint null, /* min scale if 6.0 */ | |
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 sysname null, | |
charbin tinyint null, /* 0 for char/binary types, NULL for all others */ | |
SQL_DATA_TYPE smallint not null, | |
SQL_DATETIME_SUB smallint null) | |
end | |
go | |
grant select on spt_datatype_info to public | |
go | |
/* Get case sensitivity */ | |
if 'A' = 'A' /* create dummy begin block */ | |
begin | |
declare @case smallint | |
begin tran | |
select @case = 0 | |
select @case = 1 where 'a' <> 'A' | |
/* Local Binary */ | |
insert into spt_datatype_info values | |
(45,null,null,'binary',-2,null,null,null,null,'0x',null,'length',1,0,2,null,0,null,'binary',0,-2,null) | |
/* Local Bit */ | |
insert into spt_datatype_info values | |
(50,null,null,'bit',-7,1,0,null,1,null,null,null,0,0,2,null,0,null,'bit',null,-7,null) | |
/* Local Char */ | |
insert into spt_datatype_info values | |
(47,null,null,'char',1,null,null,null,null,'''','''','length',1,@case,3,null,0,null,'char',0,1,null) | |
/* Local Datetime */ | |
insert into spt_datatype_info values | |
(61,8,2,'datetime',11,23,3,NULL,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3) | |
insert into spt_datatype_info values | |
(61,8,3,'datetime',93,23,3,NULL,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3) | |
/* Local Smalldatetime */ | |
insert into spt_datatype_info values | |
(58,4,2,'smalldatetime',11,16,0,NULL,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3) | |
insert into spt_datatype_info values | |
(58,4,3,'smalldatetime',93,16,0,NULL,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3) | |
/* Local Float */ | |
insert into spt_datatype_info values | |
(62,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null) | |
insert into spt_datatype_info values | |
(62,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null) | |
/* Local Real */ | |
insert into spt_datatype_info values | |
(59,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null) | |
insert into spt_datatype_info values | |
(59,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null) | |
/* Local Smallmoney */ | |
insert into spt_datatype_info values | |
(122,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null) | |
/* Local Money */ | |
insert into spt_datatype_info values | |
(60,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null) | |
/* Local Int */ | |
insert into spt_datatype_info values | |
(56,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null) | |
commit tran | |
end | |
go | |
if 'A' = 'A' /* create dummy begin block */ | |
begin | |
declare @case smallint | |
begin tran | |
select @case = 0 | |
select @case = 1 where 'a' <> 'A' | |
/* Local Smallint */ | |
insert into spt_datatype_info values | |
(52,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null) | |
insert into spt_datatype_info values | |
(52,2,1,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null) | |
/* Local Tinyint */ | |
insert into spt_datatype_info values | |
(48,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null) | |
/* Local Text */ | |
insert into spt_datatype_info values | |
(35,null,null,'text',-1,2147483647,null,null,2147483647,'''','''',null,1,@case,1,null,0,null,'text',0,-1,null) | |
/* Local Varbinary */ | |
insert into spt_datatype_info values | |
(37,null,null,'varbinary',-3,null,null,null,null,'0x',null,'max length',1,0,2,null,0,null,'varbinary',0,-3,null) | |
/* Local Varchar */ | |
insert into spt_datatype_info values | |
(39,null,null,'varchar',12,null,null,null,null,'''','''','max length',1,@case,3,null,0,null,'varchar',0,12,null) | |
/* Local Image */ | |
insert into spt_datatype_info values | |
(34,null,null,'image',-4,2147483647,null,null,2147483647,'0x',null,null,1,0,0,null,0,null,'image',0,-4,null) | |
if (charindex('6.00', @@version) > 0 or | |
charindex('6.50', @@version) > 0 or | |
charindex('7.00', @@version) > 0 or | |
charindex('8.00', @@version) > 0) | |
begin /* Add 6.0 data types */ | |
/* Local Decimal */ | |
insert into spt_datatype_info values /* sql server type is 'decimaln' */ | |
(55,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null) | |
/* Local Numeric */ | |
insert into spt_datatype_info values /* sql server type is 'numericn' */ | |
(63,null,null,'numeric',2 ,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null) | |
/* Identity attribute data types */ | |
/* Identity Int */ | |
insert into spt_datatype_info values | |
(56,null,null,'int identity',4,10,0,10,null,null,null,null,0,0,2,0,0,1,'int identity',null,4,null) | |
/* Identity Smallint */ | |
insert into spt_datatype_info values | |
(52,null,null,'smallint identity',5,5,0,10,null,null,null,null,0,0,2,0,0,1,'smallint identity',null,5,null) | |
/* Identity Tinyint */ | |
insert into spt_datatype_info values | |
(48,null,null,'tinyint identity',-6,3,0,10,null,null,null,null,0,0,2,1,0,1,'tinyint identity',null,-6,null) | |
/* Identity Numeric */ | |
insert into spt_datatype_info values /* sql server type is 'decmaln' */ | |
(55,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null) | |
/* Identity Numeric */ | |
insert into spt_datatype_info values /* sql server type is 'decmaln' */ | |
(63,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null) | |
end | |
if (charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin /* Add nullable type for non-8.0 server */ | |
/* Local Datetimn */ | |
insert into spt_datatype_info values | |
(111,4,2,'smalldatetime',11,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3) | |
insert into spt_datatype_info values | |
(111,4,3,'smalldatetime',93,16,0,10,16,'''','''',null,1,0,3,null,0,null,'smalldatetime',null,9,3) | |
insert into spt_datatype_info values /* sql server type is 'datetimn' */ | |
(111,8,2,'datetime',11,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3) | |
insert into spt_datatype_info values | |
(111,8,3,'datetime',93,23,3,10,16,'''','''',null,1,0,3,null,0,null,'datetime',null,9,3) | |
/* Local Floatn */ | |
insert into spt_datatype_info values /* sql server type is 'floatn' */ | |
(109,4,2,'real',7, 7,null,10,4,null,null,null,1,0,2,0,0,0,'real',null,7,null) | |
insert into spt_datatype_info values | |
(109,4,3,'real',7,24,null, 2,4,null,null,null,1,0,2,0,0,0,'real',null,7,null) | |
insert into spt_datatype_info values /* sql server type is 'floatn' */ | |
(109,8,2,'float',6,15,null,10,8,null,null,null,1,0,2,0,0,0,'float',null,6,null) | |
insert into spt_datatype_info values | |
(109,8,3,'float',6,53,null, 2,8,null,null,null,1,0,2,0,0,0,'float',null,6,null) | |
/* Local Moneyn */ | |
insert into spt_datatype_info values /* sql server type is 'moneyn' */ | |
(110,4,null,'smallmoney',3,10,4,10,12,'$',null,null,1,0,2,0,1,0,'smallmoney',null,3,null) | |
insert into spt_datatype_info values /* sql server type is 'moneyn' */ | |
(110,8,null,'money',3,19,4,10,21,'$',null,null,1,0,2,0,1,0,'money',null,3,null) | |
/* Local Intn */ | |
insert into spt_datatype_info values /* sql server type is 'intn' */ | |
(38,4,null,'int',4,10,0,10,4,null,null,null,1,0,2,0,0,0,'int',null,4,null) | |
insert into spt_datatype_info values /* sql server type is 'intn' */ | |
(38,2,null,'smallint',5,5,0,10,2,null,null,null,1,0,2,0,0,0,'smallint',null,5,null) | |
insert into spt_datatype_info values | |
(38,1,null,'tinyint',-6,3,0,10,1,null,null,null,1,0,2,1,0,0,'tinyint',null,-6,null) | |
if (charindex('6.00', @@version) > 0 or | |
charindex('6.50', @@version) > 0 or | |
charindex('7.00', @@version) > 0 or | |
charindex('8.00', @@version) > 0) | |
begin /* Add 6.0 data types */ | |
/* Local Decimaln */ | |
insert into spt_datatype_info values /* sql server type is 'decimaln' */ | |
(106,null,null,'decimal',3,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'decimal',null,3,null) | |
insert into spt_datatype_info values /* sql server type is 'decmaln' */ | |
(106,null,null,'decimal() identity',3,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'decimal() identity',null,3,null) | |
/* Local Numericn */ | |
insert into spt_datatype_info values /* sql server type is 'numericn' */ | |
(108,null,null,'numeric',2,38,0,10,null,null,null,'precision,scale',1,0,2,0,0,0,'numeric',null,2,null) | |
insert into spt_datatype_info values /* sql server type is 'decmaln' */ | |
(108,null,null,'numeric() identity',2,38,0,10,null,null,null,'precision,scale',0,0,2,0,0,1,'numeric() identity',null,2,null) | |
end | |
end | |
commit tran | |
end | |
go | |
if (charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 8.0 SQL Server.' | |
print 'Ignore the following errors.' | |
end | |
go | |
if (charindex('7.00', @@version) > 0 or | |
charindex('8.00', @@version) > 0) | |
begin | |
declare @ncase smallint | |
select @ncase = 0 | |
select @ncase = 1 where N'a' <> N'A' | |
/* Local Timestamp */ | |
insert into spt_datatype_info values | |
(0,null,null,'timestamp',-2,8,null,null,null,'0x',null,null,1,0,2,null,0,null,'timestamp',0,-2,null) | |
/* Local GUID */ | |
insert into spt_datatype_info values | |
(0,null,null,'uniqueidentifier',-11,36,null,null,null,'''','''',null,1,0,2,null,0,null,'uniqueidentifier',NULL,-11,null) | |
/* Local NChar */ | |
insert into spt_datatype_info values | |
(0,null,null,'nchar',-8,null,null,null,null,'N''','''','length',1,@ncase,3,null,0,null,'nchar',0,-8,null) | |
/* Local NVarchar */ | |
insert into spt_datatype_info values | |
(0,null,null,'nvarchar',-9,null,null,null,null,'N''','''','max length',1,@ncase,3,null,0,null,'nvarchar',0,-9,null) | |
/* Local NText */ | |
insert into spt_datatype_info values | |
(0,null,null,'ntext',-10,2147483646,null,null,2147483646,'N''','''',null,1,@ncase,1,null,0,null,'ntext',0,-10,null) | |
if (charindex('8.00', @@version) > 0) | |
begin | |
-- /* Local BIGINT */ | |
insert into spt_datatype_info values | |
(127,8,null,'bigint',-5,19,0,10,null,null,null,null,1,0,2,0,0,0,'bigint',null,-5,null) | |
-- /* Identity BIGINT */ | |
insert into spt_datatype_info values | |
(127,8,null,'bigint identity',-5,19,0,10,null,null,null,null,0,0,2,0,0,1,'bigint identity',null,-5,null) | |
-- /* sql_variant */ | |
insert into spt_datatype_info values | |
( | |
98, --ss_dtype | |
null, --fixlen | |
null, --ODBCVer | |
'sql_variant', --TYPE_NAME | |
-150, --SQL DATA TYPE | |
8000, --data_precision | |
0, --numeric_scale | |
10, --RADIX | |
8000, --length | |
null, --PREFIX | |
null, --SUFFIX | |
null, --Create Params | |
1, --Nullable | |
0, --Case sensitive | |
2, --Searchable | |
null, --UNSIGNED_ATTRIBUTE | |
0, --MONEY | |
null, --AUTO_INCREMENT | |
'sql_variant', --LOCAL TYPE NAME | |
0, --CHARBIN | |
-150, --SQL_DATA_TYPE | |
null --SQL_DATETIME_SUB | |
) | |
end | |
update spt_datatype_info set NULLABLE = 1 | |
where TYPE_NAME = 'bit' | |
update spt_datatype_info set ss_dtype = | |
isnull((select distinct xtype | |
from systypes | |
where TYPE_NAME = name OR TYPE_NAME = name+' identity' OR TYPE_NAME = name+'() identity'), | |
0) | |
end | |
go | |
create unique clustered index datatypeinfoclust on spt_datatype_info(ss_dtype,fixlen,ODBCVer,AUTO_INCREMENT) | |
go | |
dump tran master with no_log | |
go | |
print 'creating table spt_server_info' | |
go | |
create table spt_server_info ( | |
attribute_id int NOT NULL, | |
attribute_name varchar(60) NOT NULL, | |
attribute_value varchar(255) NOT NULL) | |
go | |
create unique clustered index serverinfoclust on spt_server_info(attribute_id) | |
go | |
if (charindex('7.00', @@version) = 0 and charindex('8.00', @@version) = 0) | |
begin | |
drop procedure sp_add_server_sortinfo /* not used by other servers */ | |
drop procedure sp_add_server_sortinfo75 /* not used by older servers */ | |
dump tran master with no_log | |
end | |
go | |
insert into spt_server_info | |
values (1,'DBMS_NAME','Microsoft SQL Server') | |
insert into spt_server_info | |
values (2,'DBMS_VER',@@version) | |
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 (13,'TABLE_LENGTH','30') | |
insert into spt_server_info | |
values (14,'MAX_QUAL_LENGTH','30') | |
insert into spt_server_info | |
values (15,'COLUMN_LENGTH','30') | |
if 'A' = 'a' /* If not case sensitive server */ | |
begin | |
insert into spt_server_info | |
values (16,'IDENTIFIER_CASE','MIXED') | |
end | |
else | |
begin | |
insert into spt_server_info | |
values (16,'IDENTIFIER_CASE','SENSITIVE') | |
end | |
insert into spt_server_info | |
values (17,'TX_ISOLATION','2') | |
if (charindex('6.00', @@version) > 0 or | |
charindex('6.50', @@version) > 0 or | |
charindex('7.00', @@version) > 0) | |
begin /* Add 6.0 collation sequence */ | |
insert into spt_server_info | |
select 18,'COLLATION_SEQ', | |
'charset='+t2.name+' sort_order='+t1.name | |
+' charset_num='+rtrim(convert(char(4),t1.csid))+ | |
' sort_order_num='+rtrim(convert(char(4),t1.id)) | |
from syscharsets t1, syscharsets t2, sysconfigures t3 | |
where t1.csid=t2.id and t1.id=t3.value and t3.config=1123 | |
end | |
else | |
begin /* Add 4.2x collation sequence */ | |
insert into spt_server_info | |
select 18,'COLLATION_SEQ', | |
'charset='+t2.name+' sort_order='+t1.name | |
+' charset_num='+rtrim(convert(char(4),t1.csid))+ | |
' sort_order_num='+rtrim(convert(char(4),t1.id)) | |
from syscharsets t1, syscharsets t2, sysconfigures t3 | |
where t1.csid=t2.id and t1.id=t3.value and t3.config=123 | |
end | |
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 (22,'ACCESSIBLE_TABLES','Y') | |
go | |
insert into spt_server_info | |
values (100,'USERID_LENGTH','30') | |
insert into spt_server_info | |
values (101,'QUALIFIER_TERM','database') | |
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 (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') | |
if (charindex('8.00', @@version) > 0) | |
begin | |
/* Columns may be dropped on 8.0 or later */ | |
insert into spt_server_info | |
values (108,'DROP_COLUMN','Y') | |
end | |
else | |
begin | |
insert into spt_server_info | |
values (108,'DROP_COLUMN','N') | |
end | |
if (charindex('8.00', @@version) > 0) | |
begin | |
/* Columns size may be changed on 8.0 or later */ | |
insert into spt_server_info | |
values (109,'INCREASE_COLUMN_LENGTH','Y') | |
end | |
else | |
begin | |
insert into spt_server_info | |
values (109,'INCREASE_COLUMN_LENGTH','N') | |
end | |
if (charindex('6.50', @@version) = 0 and | |
charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
insert into spt_server_info | |
values (110,'DDL_IN_TRANSACTION','N') | |
end | |
else | |
begin | |
insert into spt_server_info | |
values (110,'DDL_IN_TRANSACTION','Y') | |
end | |
if (charindex('8.00', @@version) > 0) | |
begin | |
/* Descending indexes allowed on 8.0 or later */ | |
insert into spt_server_info | |
values (111,'DESCENDING_INDEXES','Y') | |
end | |
else | |
begin | |
insert into spt_server_info | |
values (111,'DESCENDING_INDEXES','N') | |
end | |
insert into spt_server_info | |
values (112,'SP_RENAME','Y') | |
insert into spt_server_info | |
values (113,'REMOTE_SPROC','Y') | |
insert into spt_server_info | |
values (500,'SYS_SPROC_VERSION','8.00.2039') | |
go | |
if (charindex('7.00', @@version) > 0 or | |
charindex('8.00', @@version) > 0) | |
begin /* Update values for 8.0 server */ | |
update spt_server_info set attribute_value = '128' | |
where attribute_id in (12,13,14,15,100) | |
end | |
go | |
grant select on spt_server_info to public | |
go | |
print 'creating sp_column_privileges' | |
go | |
/* Procedure for pre 6.50 server */ | |
CREATE PROCEDURE sp_column_privileges ( | |
@table_name varchar(32), | |
@table_owner varchar(32) = null, | |
@table_qualifier varchar(32) = null, | |
@column_name varchar(96) = null) /* 3*32 */ | |
as | |
set nocount on | |
declare @table_id int | |
DECLARE @full_table_name varchar(65) /* 2*32 + 1 */ | |
declare @low smallint /* range of userids to check */ | |
declare @high smallint | |
declare @owner_uid smallint | |
select @low = 0, @high = 32767 | |
if @column_name is null /* If column name not supplied, match all */ | |
select @column_name = '%' | |
if @table_qualifier is not null | |
begin | |
if db_name() <> @table_qualifier | |
begin /* If qualifier doesn't match current database */ | |
raiserror 20001 '~~Rush_5~~' | |
return | |
end | |
end | |
if @table_owner is null | |
begin /* If unqualified table name */ | |
SELECT @full_table_name = @table_name | |
end | |
else | |
begin /* Qualified table name */ | |
SELECT @full_table_name = @table_owner + '.' + @table_name | |
end | |
/* Get Object ID */ | |
select @table_id = object_id(@full_table_name) | |
if (@@trancount <> 0) | |
begin /* If inside a transaction */ | |
raiserror 20003 '~~Rush_6~~' | |
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_priv1( | |
COLUMN_NAME varchar(32) NOT NULL, | |
grantor smallint NOT NULL, | |
grantee smallint NOT NULL, | |
select_privilege bit, | |
select_grantable bit, | |
insert_privilege bit, | |
insert_grantable bit, | |
update_privilege bit, | |
update_grantable bit, | |
references_privilege bit, | |
references_grantable bit, | |
uid smallint NOT NULL, | |
gid smallint NOT NULL) | |
/* | |
** insert a row for the table owner (who has all permissions) | |
*/ | |
select @owner_uid = ( | |
select uid | |
from sysobjects | |
where id = @table_id) | |
if (charindex('6.00', @@version) > 0) | |
begin | |
insert into #column_priv1 | |
select | |
c.name, | |
u.uid, | |
@owner_uid, | |
0, | |
1, | |
0, | |
1, | |
0, | |
1, | |
0, | |
1, | |
@owner_uid, | |
0 | |
from syscolumns c, sysusers u | |
where id = @table_id | |
and c.number = 0 | |
and u.uid = 1 /* grantor is dbo of database */ | |
end | |
else | |
begin | |
insert into #column_priv1 | |
select | |
c.name, | |
u.uid, | |
@owner_uid, | |
0, | |
1, | |
0, | |
1, | |
0, | |
1, | |
0, | |
0, | |
@owner_uid, | |
0 | |
from syscolumns c, sysusers u | |
where id = @table_id | |
and c.number = 0 | |
and u.uid = 1 /* grantor is dbo of database */ | |
end | |
/* | |
** now stick in a row for every column 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_priv1 | |
select distinct | |
c.name, | |
o.uid, | |
u.uid, | |
0, | |
0, | |
0, | |
0, | |
0, | |
0, | |
0, | |
0, | |
u.uid, | |
u.gid | |
from sysusers u, syscolumns c, sysobjects o | |
where o.id = @table_id | |
and c.id = o.id | |
and c.number = 0 | |
and u.gid <> u.uid | |
and u.uid <> @owner_uid | |
/* | |
** we need to create another temporary table to contain all the various | |
** protection information for the table in question | |
*/ | |
create table #protects ( | |
uid smallint NOT NULL, | |
grantor smallint NOT NULL, | |
action tinyint NOT NULL, | |
protecttype tinyint NOT NULL, | |
name varchar(32) NOT NULL) | |
insert into #protects | |
select | |
p.uid, | |
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 c.type = 'P' | |
and a.type = 'T' | |
and a.number = p.action | |
and p.action in (193,195,197,26) | |
and b.type = 'T' | |
and b.number = p.protecttype | |
and p.id = @table_id | |
and p.uid between @low and @high | |
update #column_priv1 | |
set select_privilege = 1 | |
from #protects p | |
where | |
p.protecttype = 205 | |
and p.action = 193 | |
and (p.name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (p.uid = 0 | |
or p.uid = #column_priv1.gid | |
or p.uid = #column_priv1.uid) | |
and not exists ( | |
select * from #protects | |
where | |
protecttype = 206 | |
and action = 193 | |
and (name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and ( uid = 0 | |
or uid = #column_priv1.gid | |
or uid = #column_priv1.uid)) | |
update #column_priv1 | |
set insert_privilege = 1 | |
from #protects p | |
where | |
p.protecttype = 205 | |
and p.action = 195 | |
and (p.name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (p.uid = 0 | |
or p.uid = #column_priv1.gid | |
or p.uid = #column_priv1.uid) | |
and not exists ( | |
select * from #protects | |
where | |
protecttype = 206 | |
and action = 195 | |
and (name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (uid = 0 | |
or uid = #column_priv1.gid | |
or uid = #column_priv1.uid)) | |
update #column_priv1 | |
set update_privilege = 1 | |
from #protects p | |
where | |
p.protecttype = 205 | |
and p.action = 197 | |
and (p.name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (p.uid = 0 | |
or p.uid = #column_priv1.gid | |
or p.uid = #column_priv1.uid) | |
and not exists ( | |
select * from #protects | |
where protecttype = 206 | |
and action = 197 | |
and (name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (uid = 0 | |
or uid = #column_priv1.gid | |
or uid = #column_priv1.uid)) | |
update #column_priv1 | |
set references_privilege = 1 | |
from #protects p | |
where | |
p.protecttype = 205 | |
and p.action = 26 | |
and (p.name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (p.uid = 0 | |
or p.uid = #column_priv1.gid | |
or p.uid = #column_priv1.uid) | |
and not exists ( | |
select * from #protects | |
where protecttype = 206 | |
and action = 26 | |
and (name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (uid = 0 | |
or uid = #column_priv1.gid | |
or uid = #column_priv1.uid)) | |
update #column_priv1 | |
set select_grantable = 1 | |
from #protects p | |
where | |
p.protecttype = 204 | |
and p.action = 193 | |
and (p.name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (p.uid = 0 | |
or p.uid = #column_priv1.gid | |
or p.uid = #column_priv1.uid) | |
and not exists ( | |
select * from #protects | |
where | |
protecttype = 206 | |
and action = 193 | |
and (name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and ( uid = 0 | |
or uid = #column_priv1.gid | |
or uid = #column_priv1.uid)) | |
update #column_priv1 | |
set insert_grantable = 1 | |
from #protects p | |
where | |
p.protecttype = 204 | |
and p.action = 195 | |
and (p.name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (p.uid = 0 | |
or p.uid = #column_priv1.gid | |
or p.uid = #column_priv1.uid) | |
and not exists ( | |
select * from #protects | |
where | |
protecttype = 206 | |
and action = 195 | |
and (name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and ( uid = 0 | |
or uid = #column_priv1.gid | |
or uid = #column_priv1.uid)) | |
update #column_priv1 | |
set update_grantable = 1 | |
from #protects p | |
where | |
p.protecttype = 204 | |
and p.action = 197 | |
and (p.name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (p.uid = 0 | |
or p.uid = #column_priv1.gid | |
or p.uid = #column_priv1.uid) | |
and not exists ( | |
select * from #protects | |
where | |
protecttype = 206 | |
and action = 197 | |
and (name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and ( uid = 0 | |
or uid = #column_priv1.gid | |
or uid = #column_priv1.uid)) | |
update #column_priv1 | |
set references_grantable = 1 | |
from #protects p | |
where | |
p.protecttype = 204 | |
and p.action = 26 | |
and (p.name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and (p.uid = 0 | |
or p.uid = #column_priv1.gid | |
or p.uid = #column_priv1.uid) | |
and not exists ( | |
select * from #protects | |
where | |
protecttype = 206 | |
and action = 26 | |
and (name = #column_priv1.COLUMN_NAME | |
or name = '~All') | |
and ( uid = 0 | |
or uid = #column_priv1.gid | |
or uid = #column_priv1.uid)) | |
create table #column_priv2( | |
COLUMN_NAME varchar(32) NOT NULL, | |
grantor smallint NULL, | |
grantee smallint NOT NULL, | |
PRIVILEGE varchar(32) NOT NULL, | |
IS_GRANTABLE varchar(3) NULL) | |
insert into #column_priv2 | |
select | |
COLUMN_NAME, | |
grantor, | |
grantee, | |
'SELECT', | |
'NO' | |
from #column_priv1 | |
where select_privilege = 1 and select_grantable = 0 | |
insert into #column_priv2 | |
select | |
COLUMN_NAME, | |
grantor, | |
grantee, | |
'INSERT', | |
'NO' | |
from #column_priv1 | |
where insert_privilege = 1 and insert_grantable = 0 | |
insert into #column_priv2 | |
select | |
COLUMN_NAME, | |
grantor, | |
grantee, | |
'UPDATE', | |
'NO' | |
from #column_priv1 | |
where update_privilege = 1 and update_grantable = 0 | |
insert into #column_priv2 | |
select | |
COLUMN_NAME, | |
grantor, | |
grantee, | |
'REFERENCES', | |
'NO' | |
from #column_priv1 | |
where references_privilege = 1 and references_grantable = 0 | |
insert into #column_priv2 | |
select | |
COLUMN_NAME, | |
grantor, | |
grantee, | |
'SELECT', | |
'YES' | |
from #column_priv1 | |
where select_grantable = 1 | |
insert into #column_priv2 | |
select | |
COLUMN_NAME, | |
grantor, | |
grantee, | |
'INSERT', | |
'YES' | |
from #column_priv1 | |
where insert_grantable = 1 | |
insert into #column_priv2 | |
select | |
COLUMN_NAME, | |
grantor, | |
grantee, | |
'UPDATE', | |
'YES' | |
from #column_priv1 | |
where update_grantable = 1 | |
insert into #column_priv2 | |
select | |
COLUMN_NAME, | |
grantor, | |
grantee, | |
'REFERENCES', | |
'YES' | |
from #column_priv1 | |
where references_grantable = 1 | |
select | |
convert(varchar(32),db_name()) TABLE_QUALIFIER, | |
convert(varchar(32),user_name(@owner_uid)) TABLE_OWNER, | |
@table_name TABLE_NAME, | |
COLUMN_NAME, | |
convert(varchar(32),user_name(grantor)) GRANTOR, | |
convert(varchar(32),user_name(grantee)) GRANTEE, | |
PRIVILEGE, | |
IS_GRANTABLE | |
from #column_priv2 | |
where COLUMN_NAME like @column_name | |
order by 4, 7 | |
go | |
if (charindex('6.50', @@version) = 0 and | |
charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 6.50 SQL Server.' | |
print 'Ignore the following errors.' | |
end | |
else | |
drop proc sp_column_privileges | |
go | |
/* Procedure for 6.50 server */ | |
CREATE PROCEDURE sp_column_privileges ( | |
@table_name varchar(32), | |
@table_owner varchar(32) = null, | |
@table_qualifier varchar(32) = null, | |
@column_name varchar(96) = null) /* 3*32 */ | |
as | |
declare @table_id int | |
if @column_name is null /* If column name not supplied, match all */ | |
select @column_name = '%' | |
if @table_qualifier is not null | |
begin | |
if db_name() <> @table_qualifier | |
begin /* If qualifier doesn't match current database */ | |
raiserror (15250, -1,-1) | |
return | |
end | |
end | |
if @table_owner is null | |
begin /* If unqualified table name */ | |
select @table_id = object_id(@table_name) | |
end | |
else | |
begin /* Qualified table name */ | |
select @table_id = object_id(@table_owner + '.' + @table_name) | |
end | |
select | |
convert(varchar(32),db_name()) TABLE_QUALIFIER, | |
convert(varchar(32),user_name(o.uid)) TABLE_OWNER, | |
@table_name TABLE_NAME, | |
convert(varchar(32),c.name) COLUMN_NAME, | |
convert(varchar(32),user_name(p.grantor)) GRANTOR, | |
convert(varchar(32),user_name(u.uid)) GRANTEE, | |
convert (varchar(32),case p.action | |
when 193 then 'SELECT' | |
when 195 then 'INSERT' | |
when 197 then 'UPDATE' | |
else 'REFERENCES' | |
end) PRIVILEGE, | |
convert (varchar(3),case when p.protecttype = 205 then 'NO' | |
else 'YES' | |
end) IS_GRANTABLE | |
from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c | |
where | |
c.id = @table_id | |
and c.name like @column_name | |
and c.id = p.id | |
and c.id = o.id | |
and case substring(p.columns, 1, 1) & 1 | |
when NULL then 255 /* all columns have permission */ | |
when 0 then convert(tinyint, substring(p.columns, v.low, 1)) | |
else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0))) | |
end | |
& v.high <> 0 /* permission applies to this column */ | |
and v.number <= (select count(*) from syscolumns | |
where id = @table_id) /* ranges from 1 to # of columns in table */ | |
and v.type = 'P' | |
and v.number = c.colid | |
/* expand groups */ | |
and ((p.uid = u.uid and u.uid <> u.gid) or | |
(p.uid = u.gid and u.uid <> u.gid)) | |
and p.protecttype <> 206 /* only grant rows */ | |
and p.action in (26,193,195,197) | |
and o.uid <> u.uid /* no rows for owner */ | |
and not exists ( /* exclude revoke'd privileges */ | |
select * | |
from sysprotects p1 | |
where | |
p1.protecttype = 206 | |
and p1.action = p.action | |
and p1.id = p.id | |
and p1.uid = u.uid | |
and case substring(p1.columns, 1, 1) & 1 | |
when NULL then 255 /* all columns have permission */ | |
when 0 then convert(tinyint, substring(p1.columns, v.low, 1)) | |
else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0))) | |
end | |
& v.high <> 0) /* permission applies to this column */ | |
union | |
select /* Add rows for table owner */ | |
convert(varchar(32),db_name()) TABLE_QUALIFIER, | |
convert(varchar(32),user_name(o.uid)) TABLE_OWNER, | |
@table_name TABLE_NAME, | |
convert(varchar(32),col_name(@table_id, c.colid)) COLUMN_NAME, | |
convert(varchar(32),user_name(u.uid)) grantor, | |
convert(varchar(32),user_name(o.uid)) grantee, | |
convert (varchar(32),case v.number | |
when 193 then 'SELECT' | |
when 195 then 'INSERT' | |
when 197 then 'UPDATE' | |
else 'REFERENCES' | |
end) PRIVILEGE, | |
convert(varchar(3),'YES') IS_GRANTABLE | |
from sysobjects o, master.dbo.spt_values v, sysusers u, syscolumns c | |
where | |
c.id = @table_id | |
and c.name like @column_name | |
and c.id = o.id | |
and u.uid = 1 /* grantor is dbo of database */ | |
and v.type = 'P' /* cross product to get all exposed privileges */ | |
and v.number in (26,193,195,197) | |
and not exists ( /* exclude revoke'd privileges */ | |
select * | |
from sysprotects p1 | |
where | |
p1.protecttype = 206 | |
and p1.action = v.number | |
and p1.id = o.id | |
and p1.uid = o.uid) | |
order by 4, 7 | |
go | |
if (charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 8.0 SQL Server.' | |
print 'Ignore the following errors.' | |
end | |
else | |
drop proc sp_column_privileges | |
go | |
/* Procedure for 8.0 server */ | |
CREATE PROCEDURE sp_column_privileges ( | |
@table_name sysname, | |
@table_owner sysname = null, | |
@table_qualifier sysname = null, | |
@column_name nvarchar(384) = null) /* 3*128 */ | |
as | |
declare @table_id int | |
if @column_name is null /* If column name not supplied, match all */ | |
select @column_name = '%' | |
if @table_qualifier is not null | |
begin | |
if db_name() <> @table_qualifier | |
begin /* If qualifier doesn't match current database */ | |
raiserror (15250, -1,-1) | |
return | |
end | |
end | |
if @table_owner is null | |
begin /* If unqualified table name */ | |
select @table_id = object_id(quotename(@table_name)) | |
end | |
else | |
begin /* Qualified table name */ | |
if @table_owner = N'' | |
begin /* If empty owner name */ | |
select @table_id = 0 | |
end | |
else | |
begin | |
select @table_id = object_id(quotename(@table_owner) + | |
'.' + quotename(@table_name)) | |
end | |
end | |
select | |
convert(sysname,db_name()) TABLE_QUALIFIER, | |
convert(sysname,user_name(o.uid)) TABLE_OWNER, | |
@table_name TABLE_NAME, | |
convert(sysname,c.name) COLUMN_NAME, | |
convert(sysname,user_name(p.grantor)) GRANTOR, | |
convert(sysname,user_name(u.uid)) GRANTEE, | |
convert (varchar(32),case p.action | |
when 193 then 'SELECT' | |
when 195 then 'INSERT' | |
when 197 then 'UPDATE' | |
else 'REFERENCES' | |
end) PRIVILEGE, | |
convert (varchar(3),case when p.protecttype = 205 then 'NO' | |
else 'YES' | |
end) IS_GRANTABLE | |
from sysprotects p, sysobjects o, sysusers u, master.dbo.spt_values v, syscolumns c, sysmembers m | |
where | |
c.id = @table_id | |
and c.name like @column_name | |
and c.id = p.id | |
and c.id = o.id | |
and case substring(p.columns, 1, 1) & 1 | |
when NULL then 255 /* all columns have permission */ | |
when 0 then convert(tinyint, substring(p.columns, v.low, 1)) | |
else (~convert(tinyint, isnull(substring(p.columns, v.low, 1),0))) | |
end | |
& v.high <> 0 /* permission applies to this column */ | |
and v.number <= (select count(*) from syscolumns | |
where id = @table_id) /* ranges from 1 to # of columns in table */ | |
and v.type = N'P' | |
and v.number = c.colid | |
/* expand groups - AKUNDONE: only 1 level of group unrolling here. Need more?? */ | |
and (u.uid > 0 and u.uid < 16384) | |
and ((p.uid = u.uid) or | |
(p.uid = m.groupuid and u.uid = m.memberuid)) | |
and p.protecttype <> 206 /* only grant rows */ | |
and p.action in (26,193,195,197) | |
and o.uid <> u.uid /* no rows for owner */ | |
and not exists ( /* exclude revoke'd privileges */ | |
select * | |
from sysprotects p1 | |
where | |
p1.protecttype = 206 | |
and p1.action = p.action | |
and p1.id = p.id | |
and p1.uid = u.uid | |
and case substring(p1.columns, 1, 1) & 1 | |
when NULL then 255 /* all columns have permission */ | |
when 0 then convert(tinyint, substring(p1.columns, v.low, 1)) | |
else (~convert(tinyint,isnull(substring(p.columns, v.low, 1),0))) | |
end | |
& v.high <> 0) /* permission applies to this column */ | |
union | |
select /* Add rows for table owner */ | |
convert(sysname,db_name()) TABLE_QUALIFIER, | |
convert(sysname,user_name(o.uid)) TABLE_OWNER, | |
@table_name TABLE_NAME, | |
convert(sysname,col_name(@table_id, c.colid)) COLUMN_NAME, | |
convert(sysname,user_name(u.uid)) grantor, | |
convert(sysname,user_name(o.uid)) grantee, | |
convert (varchar(32),case v.number | |
when 193 then 'SELECT' | |
when 195 then 'INSERT' | |
when 197 then 'UPDATE' | |
else 'REFERENCES' | |
end) PRIVILEGE, | |
convert(varchar(3),'YES') IS_GRANTABLE | |
from sysobjects o, master.dbo.spt_values v, sysusers u, syscolumns c | |
where | |
c.id = @table_id | |
and c.name like @column_name | |
and c.id = o.id | |
and u.uid = 1 /* grantor is 'dbo' of database */ | |
and v.type = N'P' /* cross product to get all exposed privileges */ | |
and v.number in (26,193,195,197) | |
and not exists ( /* exclude revoke'd privileges */ | |
select * | |
from sysprotects p1 | |
where | |
p1.protecttype = 206 | |
and p1.action = v.number | |
and p1.id = o.id | |
and p1.uid = o.uid) | |
order by 4, 7 | |
go | |
grant execute on sp_column_privileges to public | |
go | |
dump tran master with no_log | |
go | |
print 'creating sp_columns' | |
go | |
/* Procedure for pre-6.0 server */ | |
CREATE PROCEDURE sp_columns ( | |
@table_name varchar(96), | |
@table_owner varchar(96) = null, | |
@table_qualifier varchar(32) = null, | |
@column_name varchar(96) = null, | |
@ODBCVer int = 2) | |
AS | |
DECLARE @full_table_name varchar(193) | |
DECLARE @table_id int | |
if @ODBCVer <> 3 | |
select @ODBCVer = 2 | |
if @column_name is null /* If column name not supplied, match all */ | |
select @column_name = '%' | |
if @table_qualifier is not null | |
begin | |
if db_name() <> @table_qualifier | |
begin /* If qualifier doesn't match current database */ | |
raiserror 20001 '~~Rush_5~~' | |
return | |
end | |
end | |
if @table_name is null | |
begin /* If table name not supplied, match all */ | |
select @table_name = '%' | |
end | |
if @table_owner is null | |
begin /* If unqualified table name */ | |
SELECT @full_table_name = @table_name | |
end | |
else | |
begin /* Qualified table name */ | |
SELECT @full_table_name = @table_owner + '.' + @table_name | |
end | |
/* Get Object ID */ | |
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 = convert(varchar(32),DB_NAME()), | |
TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)), | |
TABLE_NAME = convert(varchar(32),o.name), | |
COLUMN_NAME = convert(varchar(32),c.name), | |
d.DATA_TYPE, | |
TYPE_NAME = t.name, | |
"PRECISION" = isnull(d.data_precision, convert(int,c.length)), | |
LENGTH = isnull(d.length, convert(int,c.length)), | |
SCALE = d.numeric_scale, | |
d.RADIX, | |
NULLABLE = /* set nullability from status flag */ | |
convert(smallint, convert(bit, c.status&8)), | |
REMARKS = convert(varchar(254),null), /* Remarks are NULL */ | |
COLUMN_DEF = text, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
CHAR_OCTET_LENGTH = isnull(d.data_precision, convert(int,c.length))+d.charbin, | |
ORDINAL_POSITION = convert(int, | |
( | |
select count(*) | |
from syscolumns sc | |
where sc.id = c.id | |
AND sc.number = c.number | |
AND sc.colid <= c.colid | |
)), | |
IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(c.status&8)+1,3))), | |
SS_DATA_TYPE = c.type | |
FROM | |
syscolumns c, | |
sysobjects o, | |
syscomments m, | |
master.dbo.spt_datatype_info d, | |
systypes t | |
WHERE | |
o.id = @table_id | |
AND c.id = o.id | |
AND t.type = d.ss_dtype | |
AND c.length = isnull(d.fixlen, c.length) | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
AND o.type <> 'P' | |
AND c.usertype = t.usertype | |
AND c.name like @column_name | |
AND c.cdefault *= m.id | |
AND m.colid = 1 | |
ORDER BY 17 | |
end | |
else | |
begin | |
/* this block is for the case where there IS pattern | |
matching done on the table name */ | |
if @table_owner is null /* If owner not supplied, match all */ | |
select @table_owner = '%' | |
SELECT | |
TABLE_QUALIFIER = convert(varchar(32),DB_NAME()), | |
TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)), | |
TABLE_NAME = convert(varchar(32),o.name), | |
COLUMN_NAME = convert(varchar(32),c.name), | |
d.DATA_TYPE, | |
TYPE_NAME = t.name, | |
"PRECISION" = isnull(d.data_precision, convert(int,c.length)), | |
LENGTH = isnull(d.length, convert(int,c.length)), | |
SCALE = d.numeric_scale, | |
d.RADIX, | |
NULLABLE = /* set nullability from status flag */ | |
convert(smallint, convert(bit, c.status&8)), | |
REMARKS = convert(varchar(254),null), /* Remarks are NULL */ | |
COLUMN_DEF = text, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
CHAR_OCTET_LENGTH = isnull(d.data_precision, convert(int,c.length))+d.charbin, | |
ORDINAL_POSITION = convert(int, | |
( | |
select count(*) | |
from syscolumns sc | |
where sc.id = c.id | |
AND sc.number = c.number | |
AND sc.colid <= c.colid | |
)), | |
IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(c.status&8)+1,3))), | |
SS_DATA_TYPE = c.type | |
FROM | |
syscolumns c, | |
sysobjects o, | |
syscomments m, | |
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 t.type = d.ss_dtype | |
AND c.length = isnull(d.fixlen, c.length) | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
AND o.type <> 'P' | |
AND c.usertype = t.usertype | |
AND c.name like @column_name | |
AND c.cdefault *= m.id | |
AND m.colid = 1 | |
ORDER BY 2, 3, 17 | |
end | |
go | |
if (charindex('6.00', @@version) = 0 and | |
charindex('6.50', @@version) = 0 and | |
charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 6.0 SQL Server.' | |
print 'Ignore the following error.' | |
end | |
else | |
drop proc sp_columns | |
go | |
/* Procedure for 6.0 and 6.50 server */ | |
CREATE PROCEDURE sp_columns ( | |
@table_name varchar(96), | |
@table_owner varchar(96) = null, | |
@table_qualifier varchar(32) = null, | |
@column_name varchar(96) = null, | |
@ODBCVer int = 2) | |
AS | |
DECLARE @full_table_name varchar(193) | |
DECLARE @table_id int | |
if @ODBCVer <> 3 | |
select @ODBCVer = 2 | |
if @column_name is null /* If column name not supplied, match all */ | |
select @column_name = '%' | |
if @table_qualifier is not null | |
begin | |
if db_name() <> @table_qualifier | |
begin /* If qualifier doesn't match current database */ | |
raiserror (15250, -1,-1) | |
return | |
end | |
end | |
if @table_name is null | |
begin | |
/* If table name not supplied, match all */ | |
select @table_name = '%' | |
end | |
if @table_owner is null | |
begin | |
/* If unqualified table name */ | |
SELECT @full_table_name = @table_name | |
end | |
else | |
begin | |
/* Qualified table name */ | |
SELECT @full_table_name = @table_owner + '.' + @table_name | |
end | |
/* Get Object ID */ | |
SELECT @table_id = object_id(@full_table_name) | |
if ((charindex('%',@full_table_name) = 0) and | |
(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 = convert(varchar(32),DB_NAME()), | |
TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)), | |
TABLE_NAME = convert(varchar(32),o.name), | |
COLUMN_NAME = convert(varchar(32),c.name), | |
DATA_TYPE = d.DATA_TYPE, | |
TYPE_NAME = convert(varchar(32), | |
case | |
when t.usertype > 100 or t.usertype in (18,80) then t.name | |
else d.TYPE_NAME | |
end), | |
"PRECISION" = convert(int, | |
case | |
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ | |
else isnull(convert(int,c.prec), 2147483647) | |
end), | |
LENGTH = convert(int, | |
case | |
when d.ss_dtype IN (106, 108, 55, 63) then /* decimal/numeric types */ | |
c.prec+2 | |
else | |
isnull(d.length, c.length) | |
end), | |
SCALE = convert(smallint, c.scale), | |
RADIX = d.RADIX, | |
NULLABLE = /* set nullability from status flag */ | |
convert(smallint, convert(bit, c.status&8)), | |
REMARKS = convert(varchar(254),null), /* Remarks are NULL */ | |
COLUMN_DEF = text, | |
SQL_DATA_TYPE = d.SQL_DATA_TYPE, | |
SQL_DATETIME_SUB = d.SQL_DATETIME_SUB, | |
CHAR_OCTET_LENGTH = isnull(convert(int,c.prec), 2147483647)+d.charbin, | |
ORDINAL_POSITION = convert(int, | |
( | |
select count(*) | |
from syscolumns sc | |
where sc.id = c.id | |
AND sc.number = c.number | |
AND sc.colid <= c.colid | |
)), | |
IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(c.status&8)+1,3))), | |
SS_DATA_TYPE = c.type | |
FROM | |
syscolumns c, | |
sysobjects o, | |
syscomments m, | |
master.dbo.spt_datatype_info d, | |
systypes t | |
WHERE | |
o.id = @table_id | |
AND c.id = o.id | |
AND t.type = d.ss_dtype | |
AND c.length = isnull(d.fixlen, c.length) | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
AND o.type <> 'P' | |
AND isnull(d.AUTO_INCREMENT,0) = (c.status&128)/128 | |
AND c.usertype = t.usertype | |
AND c.name like @column_name | |
AND c.cdefault *= m.id | |
AND m.colid = 1 | |
ORDER BY 17 | |
end | |
else | |
begin | |
/* this block is for the case where there IS pattern | |
matching done on the table name */ | |
if @table_owner is null /* If owner not supplied, match all */ | |
select @table_owner = '%' | |
SELECT | |
TABLE_QUALIFIER = convert(varchar(32),DB_NAME()), | |
TABLE_OWNER = convert(varchar(32),USER_NAME(o.uid)), | |
TABLE_NAME = convert(varchar(32),o.name), | |
COLUMN_NAME = convert(varchar(32),c.name), | |
DATA_TYPE = d.DATA_TYPE, | |
TYPE_NAME = convert(varchar(32), | |
case | |
when t.usertype > 100 or t.usertype in (18,80) then t.name | |
else d.TYPE_NAME | |
end), | |
"PRECISION" = convert(int, | |
case | |
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ | |
else isnull(convert(int,c.prec), 2147483647) | |
end), | |
LENGTH = convert(int, | |
case | |
when d.ss_dtype IN (106, 108, 55, 63) then /* decimal/numeric types */ | |
c.prec+2 | |
else | |
isnull(d.length, c.length) | |
end), | |
SCALE = convert(smallint, c.scale), | |
RADIX = d.RADIX, | |
NULLABLE = /* set nullability from status flag */ | |
convert(smallint, convert(bit, c.status&8)), | |
REMARKS = convert(varchar(254),null), /* Remarks are NULL */ | |
COLUMN_DEF = text, | |
SQL_DATA_TYPE = d.SQL_DATA_TYPE, | |
SQL_DATETIME_SUB = d.SQL_DATETIME_SUB, | |
CHAR_OCTET_LENGTH = isnull(convert(int,c.prec), 2147483647) + d.charbin, | |
ORDINAL_POSITION = convert(int, | |
( | |
select count(*) | |
from syscolumns sc | |
where sc.id = c.id | |
AND sc.number = c.number | |
AND sc.colid <= c.colid | |
)), | |
IS_NULLABLE = convert(varchar(254),rtrim(substring('NO YES',(c.status&8)+1,3))), | |
SS_DATA_TYPE = c.type | |
FROM | |
syscolumns c, | |
sysobjects o, | |
syscomments m, | |
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 t.type = d.ss_dtype | |
AND c.length = isnull(d.fixlen, c.length) | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
AND o.type <> 'P' | |
AND isnull(d.AUTO_INCREMENT,0) = (c.status&128)/128 | |
AND c.usertype = t.usertype | |
AND c.name like @column_name | |
AND c.cdefault *= m.id | |
AND m.colid = 1 | |
ORDER BY 2, 3, 17 | |
end | |
go | |
if (charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 7.0 SQL Server.' | |
print 'Ignore the following errors.' | |
end | |
else | |
drop proc sp_columns | |
go | |
/* Procedure for 7.0 server */ | |
CREATE PROCEDURE sp_columns ( | |
@table_name nvarchar(384), | |
@table_owner nvarchar(384) = null, | |
@table_qualifier sysname = null, | |
@column_name nvarchar(384) = null, | |
@ODBCVer int = 2) | |
AS | |
DECLARE @full_table_name nvarchar(769) | |
DECLARE @table_id int | |
if @ODBCVer <> 3 | |
select @ODBCVer = 2 | |
if @column_name is null /* If column name not supplied, match all */ | |
select @column_name = '%' | |
if @table_qualifier is not null | |
begin | |
if db_name() <> @table_qualifier | |
begin /* If qualifier doesn't match current database */ | |
raiserror (15250, -1,-1) | |
return | |
end | |
end | |
if @table_name is null | |
begin /* If table name not supplied, match all */ | |
select @table_name = '%' | |
end | |
if @table_owner is null | |
begin /* If unqualified table name */ | |
SELECT @full_table_name = quotename(@table_name) | |
end | |
else | |
begin /* Qualified table name */ | |
if @table_owner = '' | |
begin /* If empty owner name */ | |
SELECT @full_table_name = quotename(@table_owner) | |
end | |
else | |
begin | |
SELECT @full_table_name = quotename(@table_owner) + | |
'.' + quotename(@table_name) | |
end | |
end | |
/* Get Object ID */ | |
SELECT @table_id = object_id(@full_table_name) | |
if ((isnull(charindex('%', @full_table_name),0) = 0) and | |
(isnull(charindex('[', @table_name),0) = 0) and | |
(isnull(charindex('[', @table_owner),0) = 0) and | |
(isnull(charindex('_', @full_table_name),0) = 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 = convert(sysname,DB_NAME()), | |
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)), | |
TABLE_NAME = convert(sysname,o.name), | |
COLUMN_NAME = convert(sysname,c.name), | |
d.DATA_TYPE, | |
convert (sysname,case | |
when t.xusertype > 255 then t.name | |
else d.TYPE_NAME | |
end) TYPE_NAME, | |
convert(int,case | |
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ | |
else OdbcPrec(c.xtype,c.length,c.xprec) | |
end) "PRECISION", | |
convert(int,case | |
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */ | |
OdbcPrec(c.xtype,c.length,c.xprec)+2 | |
else | |
isnull(d.length, c.length) | |
end) LENGTH, | |
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)), | |
d.RADIX, | |
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')), | |
REMARKS = convert(varchar(254),null), /* Remarks are NULL */ | |
COLUMN_DEF = text, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin, | |
ORDINAL_POSITION = convert(int, | |
( | |
select count(*) | |
from syscolumns sc | |
where sc.id = c.id | |
AND sc.number = c.number | |
AND sc.colid <= c.colid | |
)), | |
IS_NULLABLE = convert(varchar(254), | |
substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)), | |
SS_DATA_TYPE = c.type | |
FROM | |
sysobjects o, | |
master.dbo.spt_datatype_info d, | |
systypes t, | |
syscolumns c | |
LEFT OUTER JOIN syscomments m on c.cdefault = m.id | |
AND m.colid = 1 | |
WHERE | |
o.id = @table_id | |
AND c.id = o.id | |
AND t.xtype = d.ss_dtype | |
AND c.length = isnull(d.fixlen, c.length) | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0)) | |
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0) | |
AND c.xusertype = t.xusertype | |
AND c.name like @column_name | |
ORDER BY 17 | |
end | |
else | |
begin | |
/* this block is for the case where there IS pattern | |
matching done on the table name */ | |
if @table_owner is null /* If owner not supplied, match all */ | |
select @table_owner = '%' | |
SELECT | |
TABLE_QUALIFIER = convert(sysname,DB_NAME()), | |
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)), | |
TABLE_NAME = convert(sysname,o.name), | |
COLUMN_NAME = convert(sysname,c.name), | |
d.DATA_TYPE, | |
convert (sysname,case | |
when t.xusertype > 255 then t.name | |
else d.TYPE_NAME | |
end) TYPE_NAME, | |
convert(int,case | |
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ | |
else OdbcPrec(c.xtype,c.length,c.xprec) | |
end) "PRECISION", | |
convert(int,case | |
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */ | |
OdbcPrec(c.xtype,c.length,c.xprec)+2 | |
else | |
isnull(d.length, c.length) | |
end) LENGTH, | |
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)), | |
d.RADIX, | |
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')), | |
REMARKS = convert(varchar(254),null), /* Remarks are NULL */ | |
COLUMN_DEF = text, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin, | |
ORDINAL_POSITION = convert(int, | |
( | |
select count(*) | |
from syscolumns sc | |
where sc.id = c.id | |
AND sc.number = c.number | |
AND sc.colid <= c.colid | |
)), | |
IS_NULLABLE = convert(varchar(254), | |
rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))), | |
SS_DATA_TYPE = c.type | |
FROM | |
sysobjects o, | |
master.dbo.spt_datatype_info d, | |
systypes t, | |
syscolumns c | |
LEFT OUTER JOIN syscomments m on c.cdefault = m.id | |
AND m.colid = 1 | |
WHERE | |
o.name like @table_name | |
AND user_name(o.uid) like @table_owner | |
AND o.id = c.id | |
AND t.xtype = d.ss_dtype | |
AND c.length = isnull(d.fixlen, c.length) | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0)) | |
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0) | |
AND c.xusertype = t.xusertype | |
AND c.name like @column_name | |
ORDER BY 2, 3, 17 | |
end | |
go | |
if (charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 8.0 SQL Server.' | |
print 'Ignore the following errors.' | |
end | |
else | |
drop proc sp_columns | |
go | |
/* Procedure for 8.0 server */ | |
CREATE PROCEDURE sp_columns ( | |
@table_name nvarchar(384), | |
@table_owner nvarchar(384) = null, | |
@table_qualifier sysname = null, | |
@column_name nvarchar(384) = null, | |
@ODBCVer int = 2) | |
AS | |
DECLARE @full_table_name nvarchar(769) | |
DECLARE @table_id int | |
if @ODBCVer <> 3 | |
select @ODBCVer = 2 | |
if @column_name is null /* If column name not supplied, match all */ | |
select @column_name = '%' | |
if @table_qualifier is not null | |
begin | |
if db_name() <> @table_qualifier | |
begin /* If qualifier doesn't match current database */ | |
raiserror (15250, -1,-1) | |
return | |
end | |
end | |
if @table_name is null | |
begin /* If table name not supplied, match all */ | |
select @table_name = '%' | |
end | |
if @table_owner is null | |
begin /* If unqualified table name */ | |
SELECT @full_table_name = quotename(@table_name) | |
end | |
else | |
begin /* Qualified table name */ | |
if @table_owner = '' | |
begin /* If empty owner name */ | |
SELECT @full_table_name = quotename(@table_owner) | |
end | |
else | |
begin | |
SELECT @full_table_name = quotename(@table_owner) + | |
'.' + quotename(@table_name) | |
end | |
end | |
/* Get Object ID */ | |
SELECT @table_id = object_id(@full_table_name) | |
if ((isnull(charindex('%', @full_table_name),0) = 0) and | |
(isnull(charindex('[', @table_name),0) = 0) and | |
(isnull(charindex('[', @table_owner),0) = 0) and | |
(isnull(charindex('_', @full_table_name),0) = 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 = convert(sysname,DB_NAME()), | |
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)), | |
TABLE_NAME = convert(sysname,o.name), | |
COLUMN_NAME = convert(sysname,c.name), | |
d.DATA_TYPE, | |
convert (sysname,case | |
when t.xusertype > 255 then t.name | |
else d.TYPE_NAME collate database_default | |
end) TYPE_NAME, | |
convert(int,case | |
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ | |
else OdbcPrec(c.xtype,c.length,c.xprec) | |
end) "PRECISION", | |
convert(int,case | |
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */ | |
OdbcPrec(c.xtype,c.length,c.xprec)+2 | |
else | |
isnull(d.length, c.length) | |
end) LENGTH, | |
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)), | |
d.RADIX, | |
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')), | |
REMARKS = convert(varchar(254),null), /* Remarks are NULL */ | |
COLUMN_DEF = text, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin, | |
ORDINAL_POSITION = convert(int, | |
( | |
select count(*) | |
from syscolumns sc | |
where sc.id = c.id | |
AND sc.number = c.number | |
AND sc.colid <= c.colid | |
)), | |
IS_NULLABLE = convert(varchar(254), | |
substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)), | |
SS_DATA_TYPE = c.type | |
FROM | |
sysobjects o, | |
master.dbo.spt_datatype_info d, | |
systypes t, | |
syscolumns c | |
LEFT OUTER JOIN syscomments m on c.cdefault = m.id | |
AND m.colid = 1 | |
WHERE | |
o.id = @table_id | |
AND c.id = o.id | |
AND t.xtype = d.ss_dtype | |
AND c.length = isnull(d.fixlen, c.length) | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0)) | |
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0) | |
AND c.xusertype = t.xusertype | |
AND c.name like @column_name | |
ORDER BY 17 | |
end | |
else | |
begin | |
/* this block is for the case where there IS pattern | |
matching done on the table name */ | |
if @table_owner is null /* If owner not supplied, match all */ | |
select @table_owner = '%' | |
SELECT | |
TABLE_QUALIFIER = convert(sysname,DB_NAME()), | |
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)), | |
TABLE_NAME = convert(sysname,o.name), | |
COLUMN_NAME = convert(sysname,c.name), | |
d.DATA_TYPE, | |
convert (sysname,case | |
when t.xusertype > 255 then t.name | |
else d.TYPE_NAME collate database_default | |
end) TYPE_NAME, | |
convert(int,case | |
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ | |
else OdbcPrec(c.xtype,c.length,c.xprec) | |
end) "PRECISION", | |
convert(int,case | |
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */ | |
OdbcPrec(c.xtype,c.length,c.xprec)+2 | |
else | |
isnull(d.length, c.length) | |
end) LENGTH, | |
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)), | |
d.RADIX, | |
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')), | |
REMARKS = convert(varchar(254),null), /* Remarks are NULL */ | |
COLUMN_DEF = text, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin, | |
ORDINAL_POSITION = convert(int, | |
( | |
select count(*) | |
from syscolumns sc | |
where sc.id = c.id | |
AND sc.number = c.number | |
AND sc.colid <= c.colid | |
)), | |
IS_NULLABLE = convert(varchar(254), | |
rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))), | |
SS_DATA_TYPE = c.type | |
FROM | |
sysobjects o, | |
master.dbo.spt_datatype_info d, | |
systypes t, | |
syscolumns c | |
LEFT OUTER JOIN syscomments m on c.cdefault = m.id | |
AND m.colid = 1 | |
WHERE | |
o.name like @table_name | |
AND user_name(o.uid) like @table_owner | |
AND o.id = c.id | |
AND t.xtype = d.ss_dtype | |
AND c.length = isnull(d.fixlen, c.length) | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0)) | |
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0) | |
AND c.xusertype = t.xusertype | |
AND c.name like @column_name | |
ORDER BY 2, 3, 17 | |
end | |
go | |
grant execute on sp_columns to public | |
go | |
dump tran master with no_log | |
go | |
print 'creating sp_databases' | |
go | |
/* Procedure for pre-7.0 server */ | |
create proc sp_databases | |
as | |
set nocount on | |
/* Use temporary table to sum up database size w/o using group by */ | |
create table #databases ( | |
DATABASE_NAME varchar(32) NOT NULL, | |
size int NOT NULL) | |
/* Insert row for each database */ | |
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, /* Convert from 2048 byte pages to K */ | |
REMARKS = convert(varchar(254),null) /* Remarks are NULL */ | |
from #databases | |
order by 1 | |
go | |
if (charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 8.0 SQL Server.' | |
print 'Ignore the following errors.' | |
end | |
else | |
drop proc sp_databases | |
go | |
/* Procedure for 8.0 server */ | |
create proc sp_databases | |
as | |
set nocount on | |
declare @name sysname | |
declare @SQL nvarchar(600) | |
/* Use temporary table to sum up database size w/o using group by */ | |
create table #databases ( | |
DATABASE_ID int NOT NULL, | |
size int NOT NULL) | |
declare c1 cursor for | |
select name from master.dbo.sysdatabases | |
where has_dbaccess(name) = 1 -- Only look at databases to which we have access | |
open c1 | |
fetch c1 into @name | |
while @@fetch_status >= 0 | |
begin | |
select @SQL = 'insert into #databases | |
select '+ convert(sysname, db_id(@name)) + ', sum(size) from ' | |
+ QuoteName(@name) + '.dbo.sysfiles' | |
/* Insert row for each database */ | |
execute (@SQL) | |
fetch c1 into @name | |
end | |
deallocate c1 | |
select | |
DATABASE_NAME = db_name(DATABASE_ID), | |
DATABASE_SIZE = size*8,/* Convert from 8192 byte pages to K */ | |
REMARKS = convert(varchar(254),null) /* Remarks are NULL */ | |
from #databases | |
order by 1 | |
go | |
grant execute on sp_databases to public | |
go | |
dump tran master with no_log | |
go | |
print 'creating sp_datatype_info' | |
go | |
/* Procedure for pre-6.0 server */ | |
create proc sp_datatype_info | |
(@data_type int = 0, @ODBCVer tinyint = 2) | |
as | |
if @ODBCVer <> 3 | |
select @ODBCVer = 2 | |
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, | |
LOCAL_TYPE_NAME = t.name, | |
MINIMUM_SCALE = d.numeric_scale, | |
MAXIMUM_SCALE = d.numeric_scale, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
NUM_PREC_RADIX = convert(int,d.RADIX), | |
INTERVAL_PRECISION = convert(smallint,NULL), | |
USERTYPE = t.usertype | |
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t | |
where | |
d.ss_dtype = t.type | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
and t.usertype *= e.user_type | |
and (t.type not in (111,109,38,110) or /* get rid of nullable types */ | |
t.usertype > 100) | |
order by 2, 12, 11, t.usertype | |
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, | |
LOCAL_TYPE_NAME = t.name, | |
MINIMUM_SCALE = d.numeric_scale, | |
MAXIMUM_SCALE = d.numeric_scale, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
NUM_PREC_RADIX = convert(int,d.RADIX), | |
INTERVAL_PRECISION = convert(smallint,NULL), | |
USERTYPE = t.usertype | |
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 (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
and t.usertype *= e.user_type | |
and (t.type not in (111,109,38,110) or /* get rid of nullable types */ | |
t.usertype > 100) | |
order by 12, 11, t.usertype | |
go | |
if (charindex('6.00', @@version) = 0 and | |
charindex('6.50', @@version) = 0 and | |
charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print 'you are installing the stored procedures ' | |
print 'on a pre 6.0 SQL Server.' | |
print 'Ignore the following errors.' | |
end | |
else | |
drop proc sp_datatype_info | |
go | |
/* Procedure for 6.0 and 6.50 servers */ | |
create proc sp_datatype_info | |
(@data_type int = 0, @ODBCVer tinyint = 2) | |
as | |
if @ODBCVer <> 3 | |
select @ODBCVer = 2 | |
if @data_type = 0 | |
select | |
convert(varchar(32),case | |
when t.usertype > 100 or t.usertype in (18,80) then t.name | |
else d.TYPE_NAME | |
end) TYPE_NAME, | |
d.DATA_TYPE, | |
convert(int,case | |
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ | |
when d.ss_dtype in (35,34) then 2147483647 /* TEXT/IMAGE */ | |
when d.ss_dtype in (55,63,106,108) and t.usertype <= 100 then @@max_precision /* DECIMAL/NUMERIC */ | |
else t.prec | |
end) "PRECISION", | |
d.LITERAL_PREFIX, | |
d.LITERAL_SUFFIX, | |
e.CREATE_PARAMS, | |
convert(smallint,case | |
when d.AUTO_INCREMENT = 1 then 0 /* IDENTITY*/ | |
else t.allownulls | |
end) NULLABLE, | |
d.CASE_SENSITIVE, | |
d.SEARCHABLE, | |
d.UNSIGNED_ATTRIBUTE, | |
d.MONEY, | |
d.AUTO_INCREMENT, | |
convert(varchar(32),case | |
when t.usertype > 100 or t.usertype in (18,80) then t.name | |
else d.TYPE_NAME | |
end) LOCAL_TYPE_NAME, | |
convert(smallint,case | |
when d.ss_dtype in (55,63,106,108) and t.usertype > 100 then t.scale | |
else d.numeric_scale | |
end) MINIMUM_SCALE, | |
convert(smallint,case | |
when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 0 then @@max_precision /* DECIMAL/NUMERIC */ | |
when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 1 then 0 /* DECIMAL/NUMERIC IDENTITY*/ | |
else t.scale | |
end) MAXIMUM_SCALE, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
NUM_PREC_RADIX = convert(int,d.RADIX), | |
INTERVAL_PRECISION = convert(smallint,NULL), | |
USERTYPE = t.usertype | |
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t | |
where | |
d.ss_dtype = t.type | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
and t.usertype *= e.user_type | |
and isnull(d.AUTO_INCREMENT,0) *= e.AUTO_INCREMENT | |
and (t.type not in (111,109,38,110,55,63) or /* get rid of nullable types */ | |
t.usertype > 100) | |
and (t.usertype <= 100 or | |
isnull(d.AUTO_INCREMENT,0) = 0) | |
order by 2, 12, 11, t.usertype | |
else | |
select | |
convert(varchar(32),case | |
when t.usertype > 100 or t.usertype in (18,80) then t.name | |
else d.TYPE_NAME | |
end) TYPE_NAME, | |
d.DATA_TYPE, | |
convert(int,case | |
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */ | |
when d.ss_dtype in (35,34) then 2147483647 /* TEXT/IMAGE */ | |
when d.ss_dtype in (55,63,106,108) and t.usertype <= 100 then @@max_precision /* DECIMAL/NUMERIC */ | |
else t.prec | |
end) "PRECISION", | |
d.LITERAL_PREFIX, | |
d.LITERAL_SUFFIX, | |
e.CREATE_PARAMS, | |
convert(smallint,case | |
when d.AUTO_INCREMENT = 1 then 0 /* IDENTITY*/ | |
else t.allownulls | |
end) NULLABLE, | |
d.CASE_SENSITIVE, | |
d.SEARCHABLE, | |
d.UNSIGNED_ATTRIBUTE, | |
d.MONEY, | |
d.AUTO_INCREMENT, | |
convert(varchar(32),case | |
when t.usertype > 100 or t.usertype in (18,80) then t.name | |
else d.TYPE_NAME | |
end) LOCAL_TYPE_NAME, | |
convert(smallint,case | |
when d.ss_dtype in (55,63,106,108) and t.usertype > 100 then t.scale | |
else d.numeric_scale | |
end) MINIMUM_SCALE, | |
convert(smallint,case | |
when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 0 then @@max_precision /* DECIMAL/NUMERIC */ | |
when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 1 then 0 /* DECIMAL/NUMERIC IDENTITY*/ | |
else t.scale | |
end) MAXIMUM_SCALE, | |
d.SQL_DATA_TYPE, | |
d.SQL_DATETIME_SUB, | |
NUM_PREC_RADIX = convert(int,d.RADIX), | |
INTERVAL_PRECISION = convert(smallint,NULL), | |
USERTYPE = t.usertype | |
from master.dbo.spt_datatype_info d, master.dbo.spt_datatype_info_ext e, systypes t | |
where | |
d.DATA_TYPE = @data_type | |
and d.ss_dtype = t.type | |
AND (d.ODBCVer is null or d.ODBCVer = @ODBCVer) | |
and t.usertype *= e.user_type | |
and isnull(d.AUTO_INCREMENT,0) *= e.AUTO_INCREMENT | |
and (t.type not in (111,109,38,110,55,63) or /* get rid of nullable types */ | |
t.usertype > 100) | |
and (t.usertype <= 100 or | |
isnull(d.AUTO_INCREMENT,0) = 0) | |
order by 12, 11, t.usertype | |
go | |
if (charindex('7.00', @@version) = 0 and | |
charindex('8.00', @@version) = 0) | |
begin | |
print '' | |
print '' | |
print 'Warning:' | |
print |