instcat.sql

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

		@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,
			convert(varchar(32),case
				when t.usertype > 100 or t.usertype in (18,80) 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 isnull(convert(int,c.prec), 2147483647)
			end) "PRECISION",
			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) LENGTH,
			SCALE = convert(smallint, c.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(convert(int,c.prec), 2147483647)+d.charbin,
			ORDINAL_POSITION = convert(int,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),
			d.DATA_TYPE,
			convert(varchar(32),case
				when t.usertype > 100 or t.usertype in (18,80) 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 isnull(convert(int,c.prec), 2147483647)
			end) "PRECISION",
			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) LENGTH,
			SCALE = convert(smallint, c.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(convert(int,c.prec), 2147483647)+d.charbin,
			ORDINAL_POSITION = convert(int,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)
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,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 <> 'P'
			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,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 <> 'P'
			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)
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_databases
go

/*	Procedure for 7.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_NAME sysname NOT NULL,
				  size int NOT NULL)

	declare c1 cursor for 
		select name from master.dbo.sysdatabases

	open c1
	fetch c1 into @name

	while @@fetch_status >= 0
	begin
		select @SQL = 'insert into #databases
				select N'''+ @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,
		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)	/* get rid of nullable types */
		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_N