;3 ,1) = 0x01 select @lsb = 3
if substring(convert(binary(4), 1), 4 ,1) = 0x01 select @lsb = 4
-- get all tables containing identity columns in this database
select id = so.id, owner = su.name, uid = so.uid, so.name, si.doampg, si.indid, idgap =
isnull(si.identitygap,0),
colname = sc.name, sc.prec, maxgap = convert(numeric(38), ceiling((@idburn * 0.0000001) *
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) + "
(burn)"
else convert(varchar, idgap) + " (identity_gap)"
end) "Maximum Identity Gap"
from #id
order by name
end
else
if @max1 <= 32 and @max2 <= 44
begin
select convert(varchar(32), owner + "." + name + "." + colname + " numeric(" +
convert(varchar(2),prec) + ")") "Owner.Table.Column datatype",
convert(varchar(44), 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
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
select @idlen = @len -- column precision (positions)
select @idlenb = (@idlen+1-((@idlen+5)/12)-(@idlen/12))/2 -- column length (bytes)
print " "
print " Table = %1! (id=%2!)", @p0, @p0_id
print " Identity column = %1! numeric(%2!)", @colname, @len
--print " OAM page = %1!", @doampg
--print " Bytes on OAM page = %1!", @idlenb
if @idgap > 0
begin
print " Max. Identity Gap = %1! (""identity_gap"" = %2!)", @idgap, @idgap
end
else
begin
set arithabort numeric_truncation off
select @maxidgap = ceiling((convert(numeric(38),@idburn) * 0.0000001) * power(@n10,@len))
print " Max. 上一页 [1] [2] [3] [4] [5] 下一页 |
