|
bstring(@objname, 1, charindex(".", @objname) - 1) != db_name()
begin
/* 17460, "Object must be in the current database." */
raiserror 17460
return (1)
end
/*
** Now check to see if the @objname is in sysobjects. It has to be either
** in sysobjects or systypes.
*/
if not exists (select *
from sysobjects
where id = object_id(@objname))
/*
** It wasn't in sysobjects so we'll check in systypes.
*/
begin -- {
select @typeid = usertype, @basetypeid = type
from systypes
where name = @objname
/*
** Time to give up -- @objname is not in sysobjects or systypes.
*/
if @typeid is NULL
begin
/* 17461, "Object does not exist in this database." */
raiserror 17461
return (1)
end
/*
** Get length factor: types based on unichar/univarchar
** are 2 bytes per character.
*/
if (@basetypeid in (select type from systypes where
name in ('unichar', 'univarchar')))
select @lenfactor = @@unicharsize
else
select @lenfactor = 1
/*
** Print help about a data type
*/
select Type_name = s.name,
Storage_type = st.name,
Length = s.length/ @lenfactor,
Nulls = s.allownulls,
Prec = s.prec,
Scale = s.scale,
Default_name = object_name(s.tdefault),
Rule_name = object_name(s.domain),
Access_Rule_name = object_name(s.accessrule),
Ident = s.ident
into #sphelp4rs
from systypes s, systypes st
where s.usertype = @typeid
and s.type = st.type
and st.name not in ("timestamp", "sysname", "longsysname", "nchar", "nvarchar")
and st.usertype < 100
exec sp_autoformat @fulltabname = #sphelp4rs,
@selectlist = "Type_name, Storage_type,Length,Prec,Scale,Nulls,Default_name,Rule_name,Access_Rule_name,'Identity' = Ident"
drop table #sphelp4rs
return (0)
end -- }
/*
** It's in sysobjects so print out the information.
*/
/*
** Define status bit values for O2_ACCESS_CONTROL
** and O2_OR_ACCESS_CONTROL.
*/
select @and_access = hextoint("0x1000000"),
@or_access = hextoint("0x4000000")
select Name = o.name,
Owner = user_name(uid),
/*
** Decode object type, and figure out if its an access rule
** type. Access rules are sub-types of the rule type; so
** first check for sysstat to be 7. Build the prefix of
** 'AND access', or 'OR access', or '' strings. The prefix
** will be concatenated to the string 'rule' as obtained
** from the other tables.
** Meanwhile, Instead of Triggers are sub_types of trigger
** type; so first check for sysstat to be 8, then build the
** prefix of "instead of" for the trigger if needed.
*/
Object_type = (case
when ((o.sysstat & 15) = 7)
then (case
when ( ((o.sysstat2 & @and_access) > 0)
and ((o.sysstat2 & @or_access) > 0))
then "OR access"
when ((o.sysstat2 & @and_access) > 0)
then "AND access"
else null
end
)
when ((o.sysstat & 15) = 8)
then (case
when (o.type = "IT")
then "instead of "
else null
end)
else null
end
)
+ (m.description + x.name)
, Object_status = convert(varchar(255), ' ')
, Create_date = o.crdate
into #sphelp5rs
from sysobjects o, master.dbo.spt_values v,
master.dbo.spt_values x, master.dbo.sysmessages m
where o.id = object_id(@objname)
and o.sysstat & 2063 = v.number
and ((v.type = "O" and
(o.type != "XP" and ((o.sysstat2 & @sqlj_proc) = 0
or o.type = "U" or o.type = "S"))
and (o.type != "RS")) or /* precomputed result set */
(v.type = "O1" and o.type = "XP") or
(v.type = "O2" and (o.sysstat2 & @sqlj_proc) != 0
and o.type != "U" and o.type != "S") or
(v.type = "O1" and o.type = "RS") or /* precomputed result set */
(v.type = "EK" and o.type = "EK"))
and v.msgnum = m.error
and isnull(m.langid, 0) = @sptlang
and ((m.error between 17100 and 17199) or
(m.error between 17587 and 17589) or
(m.error between 18903 and 18904) or
(m.error = 17588 or m.error = 17015))
and x.type = "R"
and o.userstat & -32768 = x.number
/*
** Set Object_status based on sysstat2 to show any special status the object may have.
** Currently only object status 'deallocate first text page' or 'keep first text page'
** would be displayed based on both table level (by sp_chgattribute) and db level (by
** sp_dboption) settings as below.
**
** 'deallocate first text page' will be displayed when
** o column sysstat2 of sysobjects entry has value 536870912
** o or column sysstat2 of sysobjects entry neither has value 536870912 nor
** -2147483648, but column status4 of sysdatabases entry (for current
** database) has value -2147483648.
**
** otherwise, 'keep first text |