Search all table, find PrimaryKeys with Id = int/bigint and enable Identity, autoincrement

Question

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

Answer 1

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]