July 4, 2006 at 3:20 pm
Is there an easy way to search a database for all identity columns and show the max value in the column and the max possible value suported by the datatype.
Regards
Marts
July 4, 2006 at 8:31 pm
I had a go myself, and got the results, however I wonder if anyone has a better way than this.
Marts
create table #tbl ([Table] varchar(128), [Column] varchar(128),
[DataType] varchar(50), AvailCount bigint, MaxCount bigint)
insert into #tbl
select
o.name [Table],
c.name [Column],
t.name [DataType],
case t.name
when 'tinyint' then 127
when 'smallint' then 32767
when 'int' then 2147483647
when 'bigint' then 9223372036854775807
end [MaxCount],
0
from sysobjects o
inner join syscolumns c
on o.id = c.id
inner join systypes t
on c.type = t.type
where o.xtype = 'u'
and c.status = 128
and t.name like '%int'
order by o.name, c.colorder
declare @TN varchar(128), @cn varchar(128)
declare cur cursor local fast_forward for
select
, [column] from #tbl
open cur
fetch next from cur into @TN, @cn
while @@fetch_status = 0
begin
print @TN
exec ('declare @cnt bigint
select @cnt = max(['+@cn+']) from ['+@tn+']
update #tbl set maxcount = @cnt where
= '''+@tn+''' and [column] = '''+@cn+'''
')
fetch next from cur into @TN, @cn
end
close cur
deallocate cur
select [Table], [Column], DataType, AvailCount, MaxCount,
cast(100 * (convert(float, MaxCount) /AvailCount) as decimal(5,1)) [Percent]
from #tbl
order by (convert(float, MaxCount) /AvailCount) desc
drop table #tbl
July 5, 2006 at 6:52 am
Hello,
I think the case statement is fair enough as the definitions of field size are something that is not gleaned from the tables in terms of INT etc.
The SELECT max(ident_col) might be a little hard running on some servers (especially if that column is not part of an index). It might be better to execute a "DBCC CHECKIDENT (tablename', NORESEED)" statement as it will query the system environment for the values and not perform data selections.
Cheers
Mike
July 5, 2006 at 1:17 pm
One minor quibble is that tinyints are unsigned and have a max value of 255, not 127. Also, IDENTITY fields may be decimal. You could start with this:
SELECT
quotename(user_name(so.uid)) + '.' + quotename(so.name) AS TableName,
quotename(sc.name) AS IDColumn,
CASE sc.xtype WHEN 48 THEN CAST(255 as bigint) WHEN 52 THEN 32767
WHEN 56 THEN 2147483647 WHEN 127 THEN 9223372036854775807
ELSE POWER(10.0, xprec - xscale) - 1 END AS MaxAllowed
FROM syscolumns sc
INNER JOIN sysobjects so ON so.id = sc.id
WHERE (colstat & 1) = 1
If you don't want to directly query system tables then you can use INFORMATION_SCHEMA.COLUMNS and COLUMNPROPERTY for the query. I agree that the SELECT MAX() on all identity fields could be a performance issue, but I suppose you have a reason to ask for it. IDENT_CURRENT() provides the current value easily enough, but SELECT MAX() is the most obvious way to find the maximum value in the column. DBCC CHECKIDENT returns a sentence that would have to be parsed to extract the numbers, and I'm not sure that it wouldn't have to do the equivalent of SELECT MAX() anyway. My final version would be:
CREATE
TABLE #idents (
id smallint IDENTITY PRIMARY key,
TableName nvarchar(511),
IDColumn nvarchar(255),
MaxAllowed bigint,
MaxUsed bigint,
CurrentID bigint)
GO
DECLARE @id smallint, @cmd nvarchar(4000)
INSERT INTO #idents (TableName, IDColumn, MaxAllowed)
SELECT * FROM (
SELECT quotename(table_schema) + '.' + quotename(table_name) as TableName,
column_name as IDColumn,
CASE data_type WHEN 'tinyint' THEN CAST(255 as bigint) WHEN 'smallint' THEN 32767
WHEN 'int' THEN 2147483647 WHEN 'bigint' THEN 9223372036854775807
ELSE POWER(10.0, numeric_precision - numeric_scale) - 1 END AS MaxAllowed
FROM information_schema.columns
) x
WHERE COLUMNPROPERTY(OBJECT_ID(TableName), IDColumn, 'IsIdentity') = 1
SET @id = @@ROWCOUNT
WHILE @id > 0 BEGIN
SELECT @cmd = REPLACE(REPLACE(REPLACE(
'UPDATE #idents SET MaxUsed = (SELECT MAX(<col> ) FROM <table> ),
CurrentID = IDENT_CURRENT(''<table>'')
WHERE id = <id>',
'<table>', TableName), '<col>', IDColumn), '<id>', @id), @id = @id - 1
FROM #idents WHERE id = @id
EXEC (@cmd)
END
SELECT TableName, IDColumn, MaxAllowed, MaxUsed, CurrentID
FROM #idents
GO
DROP TABLE #idents
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply