question

VijayKumar768 avatar image
0 Votes"
VijayKumar768 asked TomPhillips-1744 answered

Identity column in existing table with BIGINT

How to pull this informaiton from SQL Server 2016

Identity column in existing table with BIGINT as the data type has reference in other tables for the same column but with INT data type, instead of BIGINT. eg History CustomerPK INT links to Customers CustomerPK BIGINT
Stored Procedure, triggers or other database functions have INT variable defined for identity column in existing table with BIGINT, eg CA_FillClipAgentTable in DB1
Identity column in existin table with BIGINT as the data type has reference in LOCAL SERVER Postgres table with INT as the data type, eg RD DistributionID BIGINT in DB1, corresponds to ardDistribution DistributionID INT in Postgres

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered VijayKumar768 commented

Hi @VijayKumar768,

Thank you for posting in Microsoft Q&A.

Identity column in existing table with BIGINT as the data type has reference in other tables for the same column but with INT data type, instead of BIGINT. eg History CustomerPK INT links to Customers CustomerPK BIGINT

Please refer below query and check whether it is working.

 with cte as (
 SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER], 
        C.TABLE_SCHEMA [PKTABLE_OWNER], 
        C.TABLE_NAME [PKTABLE_NAME], 
        KCU.COLUMN_NAME [PKCOLUMN_NAME], 
        C2.TABLE_CATALOG [FKTABLE_QUALIFIER], 
        C2.TABLE_SCHEMA [FKTABLE_OWNER], 
        C2.TABLE_NAME [FKTABLE_NAME], 
        KCU2.COLUMN_NAME [FKCOLUMN_NAME], 
        C.CONSTRAINT_NAME [FK_NAME]
 FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
          ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
             AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
        INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
          ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
             AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 
          ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA 
             AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
          ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA 
             AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
             AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
 WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY')
 ,cte1 as (
 SELECT OBJECT_SCHEMA_NAME(t.object_id, db_id()) AS SchemaName,
  t.name As TableName,
  c.name as ColumnName,
  TYPE_NAME(C.USER_TYPE_ID) typename
 FROM sys.tables t 
  JOIN sys.columns c 
 ON t.object_id=c.object_id
 WHERE c.is_identity=1)
 select a.*
 from cte a
 inner join cte1 b on  db_name()=a.PKTABLE_QUALIFIER and b.SchemaName=a.PKTABLE_OWNER and b.TableName=a.PKTABLE_NAME and b.ColumnName=a.PKCOLUMN_NAME
 inner join cte1 c on db_name()=a.PKTABLE_QUALIFIER and c.SchemaName=a.FKTABLE_OWNER and c.TableName=a.FKTABLE_NAME and c.ColumnName=a.FKCOLUMN_NAME
 where (b.typename='int' and c.typename='bigint') or (c.typename='int' and b.typename='bigint')

Stored Procedure, triggers or other database functions have INT variable defined for identity column in existing table with BIGINT, eg CA_FillClipAgentTable in DB1

Actually it is difficult to acheive this requirement in TSQL. You could provide one example and more details about it or use other tools if possible.

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey Melissa,

I used above query but the out is empty?

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

Identity column in existing table with BIGINT as the data type has reference in other tables for the same column but with INT data type, instead of BIGINT

It's not possible to create a foreign key constraint between table/columns with different data types BigInt/Int. Moreover you can not create FK constraint between different databases and also not between different databases on different server systems.
Therefore you have no references you could query.





5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

There is nothing in SQL Server which will detect your "logical" links between fields. If you had foreign keys between the fields (which you cannot do between different data types), then you can run a query to detect the links.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.