|
e should never be more than 30 chars, so it's okay to
** truncate them.
*/
insert #sphelpattr (class, class_id, attribute, attribute_id, int_value,
char_value, comments)
select c.char_value,
t.class,
a.char_value,
t.attribute,
t.int_value, t.char_value, t.comments
from sysattributes t, master.dbo.sysattributes c,
master.dbo.sysattributes a
where t.object_type = "T"
and t.object = object_id(@objname)
and c.class = 0 and c.attribute = 0
and a.class = 0 and a.attribute = 1
and t.class = c.object
and t.class = a.object
and t.attribute = a.object_info1
/*
** If the table is virtually hashed, then print the list of index key
** columns and their correspondig hash factors as char_value.
*/
if(exists(select attribute from #sphelpattr
where attribute = 'hash key factors'))
begin
/* The table is Virtually Hashed */
exec sp_getmessage 19586, @msg out
print @msg
print ""
select @new_char_value = ""
select @num_keys = keycnt from sysindexes
where id = object_id(@objname)
select @key_count = 1
select @indid = min(indid)
from sysindexes
where id = object_id(@objname)
and indid > 0
and indid < 255
set nocount on
/*
** Get the list of index key columns.
*/
while (@key_count <= @num_keys)
begin
select @thiskey =
index_col(@objname, @indid, @key_count)
if (@thiskey is NULL)
begin
break
end
if(@key_count > 1)
begin
select @new_char_value = @new_char_value + ", "
end
select @new_char_value = @new_char_value + @thiskey
/*
** In current 'char_value', we have an array of hash
** factors of type 'double' that was written to
** 'char_value' as byte string.
** Hence to get the array of hash factors back:
**
** 1. Divide 'char_value' into disjoint substrings,
** each of length 8 (sizeof double).
** 2. Convert the substring into binary type.
** 3. Convert the binary value into 'double'.
**
** Once obtained the value, convert it to character
** type and append to @new_char_value
*/
select @new_char_value = @new_char_value
+ ":" +
convert(varchar(255),
convert(double precision,
convert(binary,
substring(char_value, @key_count * 8 - 7, 8))))
from #sphelpattr
where attribute = 'hash key factors'
select @key_count = @key_count + 1
end
/*
** Append the 'max_hash_key' to @new_char_value
*/
select @new_char_value = @new_char_value + ", max_hash_key"
select @new_char_value = @new_char_value +
"=" +
convert(varchar(255),
convert(double precision,
convert(binary,
substring(char_value, @key_count * 8 - 7, 8))))
from #sphelpattr
where attribute = 'hash key factors'
update #sphelpattr set char_value = @new_char_value
where attribute = 'hash key factors'
end
/*
** It's possible a cache is deleted without doing an unbind first. After
** a server reboot the binding is marked 'invalid' (int_value = 0).
** If we have such an invalid binding, don't show it in the output.
*/
delete from #sphelpattr
where class_id = 3
and attribute_id = 0
and int_value = 0
if exists (select * from #sphelpattr)
begin
exec sp_autoformat @fulltabname = #sphelpattr,
@selectlist = "'attribute_class' = class, attribute, int_value,char_value, comments"
end
drop table #sphelpattr
end
/*
** If the object is a procedure, display sysattributes information
** if there is any.
*/
if @sysstat & 15 = 4
begin
if exists (select * from sysattributes
where object_type = "P"
and object = object_id(@objname))
begin
/*
** The join with master..sysattributes here is to
** get the string descriptions for the class
** (master.dbo.sysattributes c) and attribute
** (master.dbo.sysattributes a).
|