to tables with datarow or datapage lock schemes.
*/
exec sp_getmessage 18983, @msg out
print @msg, 'ascinserts'
end
if (@sysstat2 = 32768)
begin
/* 17578, "Lock scheme is Datarows" */
exec sp_getmessage 17578, @msg out
print @msg
/* 18983, "The '%1!' attribute is not applicable
** to tables with datarow or datapage lock schemes.
*/
exec sp_getmessage 18983, @msg out
print @msg, 'ascinserts'
end
/* Server defined value for ascinserts
** if ascinserts is set, then master..sysindexes.status2=64
*/
select @opt_ind2_ascinserts = 64
print " "
select exp_rowsize "exp_row_size",
res_page_gap "reservepagegap",
fill_factor "fillfactor",
maxrowsperpage "max_rows_per_page",
isnull(identitygap, 0) "identity_gap",
(case when ((@opt_ind2_ascinserts & status2) > 0)
then 1
else 0
end
) "ascinserts"
from sysindexes
where id = object_id(@objname)
and indid <= 1
/* Server defined constant for optimistic index lock */
select @opt_ind_lock = 268435456
/* Server defined constant for dealloc first txtpg */
select @db_dealloc_ftp = hextoint('0x80000000')
select @tab_dealloc_ftp = hextoint("0x020000000")
select @tab_keep_ftp = hextoint('0x80000000')
/* Get db level status */
select @db_stat4 = status4 from master.dbo.sysdatabases
where dbid = db_id()
select @opt_ind_status = (sysstat2 & @opt_ind_lock),
@opt_text_dealloc = (sysstat2 & (@tab_dealloc_ftp | @tab_keep_ftp))
from sysobjects where id = object_id(@objname)
if (@opt_ind_status = 0)
select @opt_ind_value = 0
else
select @opt_ind_value = 1
if ((@opt_text_dealloc = @tab_dealloc_ftp) or
((@opt_text_dealloc = 0) and (@db_stat4 & @db_dealloc_ftp != 0)))
select @opt_text_value = 1
else if (@opt_text_dealloc = @tab_keep_ftp)
select @opt_text_value = 2
else
select @opt_text_value = 0
/*
** Currently, the following information will be same
** for all partitions, only need to print once for
** one of the partitions
*/
select distinct
conopt_thld "concurrency_opt_threshold",
@opt_ind_value "optimistic_index_lock",
@opt_text_value "dealloc_first_txtpg"
from systabstats
where id = object_id(@objname)
and indid <= 1
return (0)
end
else
begin
/* 17579, "Lock scheme Unknown or Corrupted" */
exec sp_getmessage 17579, @msg out
print @msg
return (1)
end
end
/*
** If the object is computed column, display
** computed column information.
** We just simply follow the way sp_helpcomputedcolumn works.
*/
if (@sysstat & 15) = 13
begin
declare @count int, @total int, @row_id int, @config_parm int, @mode int, @tab_name varchar(767)
select @tab_name = object_name(id), @mode = status3 & 1
from syscolumns
where computedcol = object_id(@objname)
/*
** Create temporary table to stored computed column info
*/
create table #helpcpc (colname varchar(255), computedcol int,
property varchar(15) null, row_id numeric identity)
/*
** Get info on computed columns
*/
if @mode != 1
begin
insert into #helpcpc(colname, computedcol, property)
select name,
computedcol,
case when (status2 & 32 = 32) then "materialized"
else "virtual" end
from syscolumns where
computedcol = object_id(@objname) and
status3 & 1 != 1
order by colid
end
/*
** Get Info on functional index keys
*/
else
begin
insert into #helpcpc(colname, computedcol)
select name, computedcol from syscolumns where
computedcol = object_id(@objname) and
status3 & 1 = 1
order by colid
end
/*
** Display the header
*/
if @mode != 1
begin
exec sp_getmessage 19456, @msg output
end
else
begin
exec sp_getmessage 19457, @msg output
end
print ""
print @msg, @tab_name
print ""
/*
** If the configuration parameter 'allow select on syscomments.text'
** is set to 0, then the user can access the text ONLY in the
** following cases
**
** 1. if the user has sa_role
** 2. if the object is owned by the user
**
*/
select @config_parm = value
from master.dbo.syscurconfigs
where config = 258
if @config_parm = 0 and user_id() != 1
begin
/*
** The object needs to be owned by the user
*/
if not exists (select name from sysobjects
where uid = user_id()
and id = object_id(@objname))
begin
/*
** Inform the user the text cannot be displayed and
** print the rest info before return
*/
if @mode != 1
begin
exec sp_getmessage 19334, @msg output
print @msg
exec sp_autoformat #helpcpc, "'Column_Name' = colname,
Property = property"
end
else
begin
exec sp_getmessage 19335, @msg output
print @msg
exec sp_autoformat #helpcpc,
"'Internal_Index_Key_Name' = colname"
end
drop table #helpcpc
return(0)
end
end
/*
** Now display the text
*/
create table #helptxt (text varchar(255), row_id numeric (10) identity)
create table #helpname (colname varchar(255), property varchar(15) null)
select @count = 1
select @total = max(row_id) + 1 from #helpcpc
while @count < @total
begin
insert into #helpname(colname, property)
select colname, property from #helpcpc where row_id = @count
if @mode != 1
begin
exec sp_autoformat #helpname, "'Column_Name' = colname,
Property = property"
end
else
begin
exec sp_autoformat #helpname,
"'Internal_Index_Key_Name' = colname"
end
/*
** See if the object is hidden (SYSCOM_TEXT_HIDDEN will be set)
*/
if exists (select 1 from syscomments m, #helpcpc h where
(m.status & 1 = 1) and
m.id = h.computedcol and
h.row_id = @count)
begin
exec sp_getmessage 19337, @msg output
print @msg
print ""
delete #helpname
select @count = @count + 1
continue
end
insert #helptxt (text) select text from syscomments, #helpcpc
where row_id = @count and id = computedcol order by colid2, colid
print ""
if @mode != 1
begin
exec sp_autoformat #helptxt, "Text = text"
end
else
begin
select @row_id = min(row_id) from #helptxt
update #helptxt set text = right(text, char_length(text)-4)
where row_id = @row_id
select @row_id = max(row_id) from #helptxt
update #helptxt set text = left(text, char_length(text)-13)
where row_id = @row_id
exec sp_autoformat #helptxt, "Expression = text"
end
print ""
select @count = @count + 1
delete #helpname
delete #helptxt
end
drop table #helpcpc, #helpname, #helptxt
end
return (0)
一行一行判断，找下去。
我们发现在源码的556行“if (@sysstat & 15) in (1, 2, 3)”有我们想要的结果，这段IF语句是将表结果查询之后插入一个临时表#helptype中，然后再进行查询出来的。
我们不要那么麻烦，只取里面的三列：字段名，字段类型，字段长度。
当然你想封装成存储过程也可以的，但不能使用desc就是。
select isnull(c.name, 'NULL') '字段名',
t.name '字段类型',
c.length '字段长度'
from syscolumns c, systypes t, sysxtypes x
where c.id = object_id('表名')
and c.usertype *= t.usertype
and c.xtype *= x.xtid;
|