power(@n10,prec)))
into #id
from sysindexes si, syscolumns sc, sysobjects so, sysusers su
where si.indid < 2
and so.type = "U"
and sc.status & 128 = 128
and so.id = sc.id
and so.id = si.id
and so.uid = su.uid
select @ntab = @@rowcount
set arithabort numeric_truncation off
if @p0 = null
begin
-- display all tables with identity columns in the current DB
if @ntab = 0
begin
print " There are no tables with an identity column in database '%1!'", @dbname
return 0
end
print " Tables with an identity column in database '%1!':", @dbname
print " "
select @max1 = max(char_length(owner + "." + name + "." + colname)) + 12 from #id
select @max2 = max(char_length(convert(varchar(38), maxgap))) + 7 from #id
if @max1 <= 52 and @max2 <= 24
begin
select convert(varchar(52), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(24), case when idgap = 0 then convert(varchar(38),maxgap) + "
from #id
order by name
end
else
begin
select convert(varchar(78), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(50), case when idgap = 0 then convert(varchar(38),maxgap) + "
(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end
print " "
print " Legend:"
print " (burn) : gap size is determined by ""identity burning set factor"" "
if @v = 12
begin
print " (identity_gap) : gap size is determined by the ""identity_gap"" setting"
end
print " "
print " Current value for ""identity burning set factor"" = %1! (=%2!%%)", @idburn, @idburnpct
return 0
end
if charindex("sa_role", show_role()) = 0
begin
print "You must have 'sa_role' to run this procedure."
return -1
end
if charindex("sybase_ts_role", show_role()) = 0
begin
print "You must have 'sybase_ts_role' to run this procedure."
return -1
end
-- get some info on the object
select @p0_id = object_id(@p0)
if @p0_id = NULL
begin
print " Error: '%1!' is not a user table.", @p0
return -1
end
if @p0 not like "%.%"
begin
select @ntab = count(*) from sysobjects where name = @p0 and type = "U"
if @ntab > 1
begin
print " %1! tables named '%2!' exist in this database:", @ntab, @p0
print " "
select owner + "." + name "owner.table_name"
from #id where name = @p0 order by uid
print " "
print " Specify 'owner.table_name' to identify the table."
return -1
end
end
select @doampg = doampg, @indid = indid, @idgap = idgap
from #id where id = @p0_id
if not exists (select 1 from syscolumns where id = @p0_id and status & 128 = 128)
begin
print " Error: Table '%1!' does not have an identity column", @p0
return -1
end
-- retrieve the length of the numeric identity column as declared when the
-- table was created
select @len = prec, @colname = name
from syscolumns where id = @p0_id and status & 128 = 128
if @@rowcount = 0
begin
print " Error: Could not retrieve length of identity column for table '%1!'", @p0
return -1
end
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
