Search code examples
t-sqlcursorreport

Return Next Available PK in a Collection of Tables

I'm building a report from a series of known tables and their primary keys, e.g.:

BOOKS.bookid

AUTHORS.authorid

GENRE.genreid

What I would like to do is build a t-sql report that simply shows the table, the primary key, and the next available PK, e.g.:

**tabl_name       prim_key        avail_key**

BOOKS             BOOKID          281

AUTHORS           AUTHORID        29

GENRE             GENREID         18

I already have the table name and its PK by using the information_schema, but somehow joining that with the actual table to derive its next available PK is proving elusive. I'm guessing there's some sort of dynamic sql with cursors solution, but that's maxing my sql skills out.

Solution

  • Try this:

    SELECT Col.TABLE_NAME, Col.Column_Name, ident_current(Col.TABLE_NAME) from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '

    By the way, most of the above came from this answer:

    https://stackoverflow.com/a/96049/37613