Identity column in existing table with BIGINT

Question

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

Answer 1

Hi @Vijay Kumar ,

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.

Answer 2

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.

Answer 3

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.