instcat.sql

来自「VB6.0安装软件.代码和安装软件一起.用起来很不错.」· SQL 代码 · 共 2,211 行 · 第 1/5 页

/*
**	INSTCAT.SQL
**	Installs catalog stored procedures on the Microsoft SQL Server.
**	Copyright 1992-1998, Microsoft Corp.	All rights reserved.
*/

/*
NOTE:  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
('00' now), and bbb is the build number.
insert into spt_server_info
	values (500, 'SYS_SPROC_VERSION', '7.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 = '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

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_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_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


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)
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)
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)
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)
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
go
if (charindex('7.00', @@version) > 0)
begin	/*	Update usertypes for 7.00 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)
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)
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,