0

I have a large list of tables and due to change in middleware platform, the middleware is no longer creating the auto-increment on Add New Record at the app layer, as a result, I need to do this in the DB.

How can I Search all tables, find PrimaryKeys with Id = int/bigint ?

  1. To enable/toggle all tables PrimaryKeys with Id = int/bigint and turn-on Identity with auto-increment
  2. To disable Identity identity & auto-increment (for some tests, which middle ware is still failing)

Edit:

SET IDENTITY_INSERT gettableWithIdentity ON

INSERT gettableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (SetAnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT gettableWithIdentity OFF
3
  • 2
    Have you considered requesting/demanding a list of tables affected by this change from the middleware developers? What you're doing is risky as you may change tables that shouldn't be changed, and your testing may not catch all issues. Also, switching back and forth will mean something like this: stackoverflow.com/questions/1049210/… and that doesn't even consider removing and reapplying indexes etc.
    – T.H.
    Feb 23, 2017 at 15:20
  • You can't enable/disable an identity property on an existing column. You have to drop & re-add the column (methods for this are covered in the question T.H. links to, just thought this was an important point to make upfront). Feb 23, 2017 at 17:01
  • @GarethLyons They want me to support both old & New frameworks during transition, good catch on the drop and & read. Point well noted... its a lot of risks, I do not know what else options to bring to them. Feb 23, 2017 at 19:22

1 Answer 1

2

the script below will list all the primary keys, that have at least one int or bigint in their columns. You can filter further or tailor the query more but it should be a good starting point and hope it helps.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 


SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
    , OBJECT_NAME(p.object_id) AS [Table]
    , i.name AS [Index]
    , p.partition_number
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
    ,K.increment_value as IncrementValue
    ,K.last_value as LastValue
    ,K.seed_value as SeedValue
    ,k.is_nullable
    ,k.is_identity
    ,k.is_filestream
    ,k.is_replicated
    ,k.is_not_for_replication
FROM sys.partitions p

INNER JOIN sys.indexes i 
        ON p.object_id = i.object_id
       AND p.index_id = i.index_id


INNER JOIN SYS.TABLES S 
         ON S.object_id = P.object_id

LEFT OUTER JOIN sys.identity_columns K
             ON P.object_id = K.object_id

WHERE 1=1

  AND EXISTS ( SELECT 1 
                    FROM SYS.COLUMNS C
              INNER JOIN sys.types AS t 
                         ON c.user_type_id=t.user_type_id
                   WHERE i.object_id = c.object_id
                   AND T.user_type_id IN (127,56)  -- ONLY BIGINT AND INT
             )

  AND I.is_primary_key = 1

  -- AND i.index_id < 2  -- GET ONLY THE CLUSTERED INDEXES - IF EXISTS ANY
                      -- get heaps too

  --AND k.is_identity = 1 -- GET ONLY THE IDENTITY COLUMNS


ORDER BY [Schema], [Table], [Index]
1
  • works well to get the lis, thanks how do you toggle the values... thats what I am struggling with - I am posting some of the identity code above Feb 23, 2017 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.