|
Save existing Object_status information */
(case
when (Object_status != "") then Object_status + ", "
else null
end) +
/* refresh policy */
(case
when ((@sysstat3 & 8) = 8) then "manual"
when ((@sysstat3 & 16) = 16) then "immediate"
end) + ", " +
/* state */
(case
when ((@sysstat3 & 32) = 32) then "disabled"
else "enabled"
end) + ", " +
/* query rewrite state */
(case
when ((@sysstat3 & 64) = 64) then "disabled for QRW"
else "enabled for QRW"
end)
end -- }
update #sphelp5rs
set Object_status = (select description
from master.dbo.sysmessages
where error = 17661
and isnull(langid, 0) = @sptlang)
where datalength(Object_status) < 2
exec sp_autoformat @fulltabname = #sphelp5rs
drop table #sphelp5rs
/*
** Objects have the following value for sysstat & 15:
** 0 - any/illegal object
** 1 - system table
** 2 - view
** 3 - user table
** 4 - sproc
** 5 - predicate
** 6 - default
** 7 - rule
** 8 - trigger
** 9 - referential constraint
** 10 - sql function
** 11 - extended type
** 12 - sqlj function
** 13 - computed column
** 14 - partition condition
** 15 - encryption key
**
**
** If the object is a system table, view, or user table, we want to check
** out the objects columns here.
*/
select @sysstat = sysstat, @sysstat2 = sysstat2
from sysobjects
where id = object_id(@objname)
/*
** Fix of bug 91669:
** For the current design, a view having nameless column may be created,
** e.g. create view view1 as select sum(column1) from table1.
** In this case, c.name is NULL. Using builtin function isnull() to
** make the following query work.
*/
select @encrypted_col = hextoint("0x00000080")
, @decrypt_def = hextoint("0x00001000")
, @inrowlob = hextoint("0x00040000")
, @notruncate = hextoint("0x00200000")
-- Report on the following "table" objects:
-- 1: System tables
-- 2: Views
-- 3: User tables
--
if (@sysstat & 15) in (1, 2, 3)
begin
select Column_name = isnull(c.name, 'NULL'),
Col_order = colid,
Type = isnull(convert(char(30), x.xtname),
isnull(convert(char(30),
get_xtypename(c.xtype, c.xdbid)),
t.name)),
Length = c.length,
In_row_Len = c.inrowlen,
Prec = c.prec,
Scale = c.scale,
Nulls = convert(bit, (c.status & 8)),
Not_compressed = convert(bit, (isnull(c.status2, 0) & 131072)),
Lob_compression_level = c.lobcomp_lvl,
Default_name = object_name(c.cdefault),
Rule_name = object_name(c.domain),
Access_Rule_name = object_name(c.accessrule),
Computed_Column_object =
case when (c.status3 & 1) = 1
then object_name(c.computedcol)+
" (functional index key)"
when (c.status2 & 32) = 32
then object_name(c.computedcol)+" (materialized)"
when (c.status2 & 16) = 16
then object_name(c.computedcol)+" (virtual)"
else object_name(c.computedcol)
end,
rtype = t.type, utype = t.usertype, xtype = c.xtype,
Ident = convert(bit, (c.status & 0x80)),
Encrypted =
case when (c.status2 is null) then 0
when (c.status2 & @encrypted_col) > 0 then 1
end,
Decrypt_Default_name =
case when (c.status2 is null) then NULL
when (c.status2 & @decrypt_def) > 0 then
(select object_name(a.object)
from sysattributes a
where a.class = 25
and a.attribute = 1
and a.object_info1 = c.id
and a.object_info2 = c.colid)
else NULL
end,
Object_storage =
case
when ((isnull(c.status2,0) & @inrowlob) != 0)
then "in row"
when (c.xstatus is null) then NULL
when (c.xstatus & 1) = 1 then "off row"
else "in row "
end,
Varbinary_is_truncated =
case when (c.status2 is null) then NULL
when (c.status2 & @notruncate) > 0 then 0
else 1
end
into #helptype
from syscolumns c, systypes t, sysxtypes x
where c.id = object_id(@objname)
and c.usertype *= t.usertype
and c.xtype *= x.xtid
/*
** We truncate extended type names >30 characters to 30,
** and print them with a trailing "+" character.
*/
update #helptype
set Type = substring(Type, 1, 29) + "+"
where xtype is not null
and substring(Type, 29, 1) != " "
/* Handle National Characters */
update #helptype
set Length = Length / @@ncharsize
where (rtype = 47 and utype = 24)
or (rtype = 39 and utype = 25)
/* Handle unichar/univarchar */
update #helptype
set Length = Length / @@unicharsize
where rtype in (select type from systypes
where name in ('unichar', 'univarchar'))
/* Handle unsigned types by outputing user syntax */
update #helptype
set Type = "unsigned " +
substring(Type, charindex("u", Type) + 1, 30)
where utype in (44, 45, 46)
/*
** Construct the SQL query against #helptype. Be careful not to
** exceed 255 characters, or the string will get truncated.
*/
select @sqltext = "Column_name,Type, Length"
/* Display in-row length only if there are any in-row LOBs */
if exists (select 1 from #helptype
where In_row_Len is not null)
begin
/* Convert bytes to unichars for length of unitext */
update #helptype
set In_row_Len = In_row_Len / @@unicharsize
where rtype in (select type from systypes
where name = 'unitext')
select @sqltext = @sqltext + ",In_row_Len "
end
select @sqltext = @sqltext
+ ", Prec,Scale,Nulls,Not_compressed,Default_name,Rule_name,Access_Rule_name,Computed_Column_object,'Identity' = Ident"
/* Display the Object_storage only if there are object columns. */
if exists (select * from #helptype
where Object_storage is not null)
begin
select @sqltext = @sqltext + ", Object_storage "
end
/*
** Display the Encrypted column status only if there are encrypted
** columns.
*/
if exists (select * from #helptype
where Encrypted != 0)
begin
select @sqltext = @sqltext + ", Encrypted "
end
/*
** Display the Decrypt_Default_name only if there
** a |