Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Script to check current identity of Tables Expand / Collapse
Author
Message
Posted Sunday, March 7, 2010 6:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:24 AM
Points: 14, Visits: 454
Comments posted to this topic are about the item Script to check current identity of Tables


Post #878379
Posted Wednesday, March 17, 2010 5:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 1,511, Visits: 2,710
SELECT t.name, IDENT_CURRENT(t.name) FROM sys.tables t
WHERE IDENT_CURRENT(t.name) IS NOT NULL
ORDER BY 1

Post #884566
Posted Thursday, March 18, 2010 3:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:15 AM
Points: 78, Visits: 115
Hello

You script is using compatibility views. I prefer using dedicated views even if I need to have several version for different SQL Server version.

Here is my SQL script for SQL Server 2005-2008 which source is from this site
http://vyaskn.tripod.com/sql_server_check_identity_columns.htm
and that I have modified a little to manage 'numeric' and 'decimal' and use directly "sys.identity_columns" system views dedicated for this purpose

Regards
Pascal


--
-- liste les colonnes IDENTITY avec leur max current value et le taux d'usage en fonction du type choisi
--
SELECT
QUOTENAME(SCHEMA_NAME(tab.[schema_id])) + '.' + QUOTENAME(tab.[name]) AS [TableName],
col.[name] AS [ColumnName],
typ.[name] AS [DataType],
col.[last_value] AS [CurrentIdentityValue],
col.[precision] AS [PrecisionValue],
CAST(ROUND(CASE col.[system_type_id]
WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / 9223372036854775807
WHEN 106 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / CONVERT(NUMERIC(38,0),REPLICATE(9,col.[precision]))
WHEN 108 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / CONVERT(NUMERIC(38,0),REPLICATE(9,col.[precision]))
WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(tab.[schema_id]) + '.' + tab.[name]) * 100.) / 255
END,2) as NUMERIC(4,2)) AS [PercentageUsed]
FROM
sys.identity_columns AS col
INNER JOIN sys.tables AS tab ON tab.[object_id] = col.[object_id]
INNER JOIN sys.types AS typ ON col.[system_type_id] = typ.[system_type_id]
WHERE
col.[is_identity] = 1
ORDER BY
[PercentageUsed] DESC

Post #885331
Posted Thursday, March 18, 2010 7:30 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:31 AM
Points: 1,511, Visits: 2,710
If any of your tables are at approx 99.995% or higher, the script errors out with:

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

...so you need to change
as NUMERIC(4,2)) AS [PercentageUsed]
to
as NUMERIC(5,2)) AS [PercentageUsed]
Post #886017
Posted Friday, March 19, 2010 9:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, August 1, 2014 1:15 AM
Points: 78, Visits: 115
Oh yes ! you're right, thanks !
Post #886518
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse