Hi,I have a need to check the extent of usage of the primary keys in my database. The catch is that, I don't use any identity columns in my database. A solution exists out there for tables with identity columns using the IDENT_CURRENT() function of SQL Server. But in this case, I need to find the max of each table's primary key. For this purpose I would have to refer to the history tables to get the ids to check for records which were deleted as well.I am unable to get together a query to perform this activity. Please refer to the existing code. SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName, c.name AS ColumnName, i.name AS IndexName, CASE c.system_type_id WHEN 127 THEN 'bigint' WHEN 56 THEN 'int' WHEN 52 THEN 'smallint' WHEN 48 THEN 'tinyint' END AS 'DataType', IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS CurrentIdentityValue, CASE c.system_type_id WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 9223372036854775807 WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 2147483647 WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767 WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255 END AS 'PercentageUsed' FROM sys.tables AS t INNER JOIN sys.indexes AS i ON t.[object_id] = i.[object_id] INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id INNER JOIN sys.columns AS c ON t.[object_id] = c.[object_id] AND ic.column_id = c.column_id WHERE i.is_primary_key = 1 ORDER BY PercentageUsed DESC
Get Primary Key Usage Percentage
Posted - 2010-07-08 : 05:06:51