instcat.sql

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

		SQL_DATETIME_SUB	smallint	null)
end
go

grant select on spt_datatype_info to public
go

/* Get case sensitivity */
/*(leih 10/8/96) SID: Part I. break into two batches to avoid OOM error */
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,2,null,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,10,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,10,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,10,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,10,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

/*(leih 10/8/96) SID: Part II. break into two batches to avoid OOM error */
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)
	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)
	begin	/*	Add nullable type for non-Sphinx 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)
		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)
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
		declare @case smallint

		select @case = 0
		select @case = 1 where 'a' <> '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,@case,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,@case,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,@case,1,null,0,null,'ntext',0,-10,null)

		update spt_datatype_info set nullable = 1
			where TYPE_NAME = 'bit'

		update spt_datatype_info set ss_dtype = (select xtype from systypes
			where TYPE_NAME like name+'%')
	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

/* 
** (leih 2/11/98) 
** The following SP is shared by instcat.sql and the server to add information
** related to server language collations. The server calls it at the end of
** collation change to update the catalog with the new language collations
** Everything in this SP should NOT use tempdb.  
*/
create procedure sp_add_server_sortinfo
as
-- spt_server_info has unigue clustered index on attribute_id
delete spt_server_info where attribute_id = 16
delete spt_server_info where attribute_id = 18

--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 master.dbo.syscharsets t1, master.dbo.syscharsets t2, master.dbo.sysconfigures t3
--	where t1.csid=t2.id and t1.id=t3.value and t3.config in (123,1123)
declare @sortid int, @csid int, @sortname varchar(255), @attribute_value varchar(255)
select @sortid = value from sysconfigures where config = 1123
select @csid = csid, @sortname = name from master.dbo.syscharsets where id = @sortid
select @attribute_value = 'charset='+ name +' sort_order=' + @sortname
		+ ' charset_num=' + rtrim(convert(char(4),@csid))+
		' sort_order_num=' + rtrim(convert(char(4),@sortid))
		from syscharsets where id = @csid

insert into spt_server_info
		values (18,'COLLATION_SEQ', isnull(@attribute_value, ' '))

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

go

exec sp_add_server_sortinfo
go

if (charindex('7.00', @@version) = 0)
	begin
		drop procedure sp_add_server_sortinfo	/* not used by older servers */
		dump tran master with no_log
	end
go


/*(leih 10/8/96) SID: Part I break into two batches to avoid OOM error */
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')
insert into spt_server_info
	values (17,'TX_ISOLATION','2')
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

/*(leih 10/8/96) SID: Part II. break into two batches to avoid OOM error */
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')
insert into spt_server_info
	values (108,'DROP_COLUMN','N')
insert into spt_server_info
	values (109,'INCREASE_COLUMN_LENGTH','N')
if (charindex('6.50', @@version) = 0 and
	charindex('7.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
insert into spt_server_info
	values (111,'DESCENDING_INDEXES','N')
insert into spt_server_info
	values (112,'SP_RENAME','Y')
insert into spt_server_info
	values (113,'REMOTE_SPROC','Y')
insert into spt_server_info
	values (500,'SYS_SPROC_VERSION','7.00.481')
go

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