instcat.sql

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

	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 all
	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.suid = 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)
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_column_privileges
go

/*	Procedure for 7.00 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 = '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 all
	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 = '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,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,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)
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