-1

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.

tshepang
  • 12,111
  • 21
  • 91
  • 136
Stack Undertow
  • 3
  • 2

1 Answers1

0

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

Community
  • 1
  • 1
acfrancis
  • 3,569
  • 25
  • 21
  • Thanks acfrancis: the query returned null for the ident_current field. Is there special permissions for this to work? – Stack Undertow Oct 24 '13 at 08:30
  • ``ident_current()`` returns the next value for columns with the identity property (in other words, where SQL Server auto generates the next value). For tables where the PK is not identity, ``ident_current()`` will return null but that shouldn't be an issue because you (and not SQL Server) control the next PK value that will be inserted into those tables. – acfrancis Oct 24 '13 at 09:20
  • I would give your answer a up but I don't have enough rep; thanks again. – Stack Undertow Oct 24 '13 at 11:26