CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
user2943263
user2943263

Reputation: 53

How may I find all primary keys in a database and their identity and seed values?

The following query returns a listing of all my database primary keys and additional columns.

SELECT  *
FROM    sysobjects AS s
WHERE   xtype='pk'

None of the aforementioned additional columns are related to a primary key's identity, seed value, increment value, etc.

What query should I use, or how may I find all primary keys in a database and their identity and seed values?

Upvotes: 1

Views: 4091

Answers (2)

user2943263
user2943263

Reputation: 53

Here is an alternate answer b/c I combined selects and added create and modified dates for sorting and schema name for possible dupes between schemas b/c key names are unique:

SELECT  schema_name = ( SELECT top 1 pk.CONSTRAINT_SCHEMA FROM information_schema.table_constraints pk INNER JOIN information_schema.key_column_usage c ON c.TABLE_NAME = pk.TABLE_NAME WHERE pk.CONSTRAINT_NAME like '%' + i.name + '%')
        ,object_name(i.object_id) tableName, i.name indexName, c.name columnName, c.is_identity 
        ,ident_seed(object_name(i.object_id)) seed
        ,ident_incr(object_name(i.object_id)) increment
        ,ident_current(object_name(i.object_id)) lastAssignedId
        ,CreateDate = ( SELECT s.crdate FROM sysobjects s INNER JOIN (SELECT COUNT(1) cnt, xtype FROM sysobjects group by xtype) c ON  s.xtype = c.xtype WHERE s.name = i.name)
        ,ModifiedDate = (SELECT s.refdate FROM sysobjects s INNER JOIN (SELECT COUNT(1) cnt, xtype FROM sysobjects group by xtype) c on  s.xtype = c.xtype WHERE s.name = i.name)
FROM    sys.indexes i
    INNER JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
    INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE   i.is_primary_key = 1 
ORDER BY    ModifiedDate, CreateDate DESC

Upvotes: 1

JohnLBevan
JohnLBevan

Reputation: 24410

UPDATED

(now uses identity_columns table instead of the IDENT_... functions)

SELECT  object_name(i.object_id) tableName,
        i.name indexName,
        c.name columnName,
        c.is_identity,
        idc.seed_value,
        idc.increment_value,
        idc.last_value
FROM sys.indexes i
    INNER JOIN sys.index_columns ic ON  ic.object_id = i.object_id AND ic.index_id = i.index_id
    INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    LEFT OUTER JOIN sys.identity_columns idc ON idc.object_id = c.object_id AND idc.column_id = c.column_id
WHERE i.is_primary_key = 1

Upvotes: 5

Related Questions