SYS.FOREIGN_KEYS

Some parts in the output code are omitted for clarity reasons.

Description

Contains a row per object that is a FOREIGN KEY constraint (SQLServer Documentation).

The columns for FOREIGN KEY (sys.foreign_keys) are the following:

Column name	Data type	Description	Has equivalent column in Snowflake
<Columns inherited from sys.objects>	-	For a list of columns that this view inherits, see sys.objects (Transact-SQL).	Partial
referenced_object_id	int	ID of the referenced object.	No
key_index_id	int	ID of the key index within the referenced object.	No
is_disabled	bit	FOREIGN KEY constraint is disabled.	No
is_not_for_replication	bit	FOREIGN KEY constraint was created by using the NOT FOR REPLICATION option.	No
is_not_trusted	bit	FOREIGN KEY constraint has not been verified by the system.	No
delete_referential_action	tinyint	The referential action that was declared for this FOREIGN KEY when a delete happens. See SQLServer Documentation.	No
delete_referential_action_desc	nvarchar(60)	Description of the referential action that was declared for this FOREIGN KEY when a delete occurs. See SQLServer Documentation.	No
update_referential_action	tinyint	The referential action that was declared for this FOREIGN KEY when an update happens. See SQLServer Documentation.	No
update_referential_action_desc	nvarchar(60)	Description of the referential action that was declared for this FOREIGN KEY when an update happens. See SQLServer Documentation.	No
is_system_named	bit	1 = Name was generated by the system. 0 = Name was supplied by the user.	No

The inherited columns from sys.objects are the following:

For more information, review the sys.objects documentation.

Column name	Data type	Description	Has equivalent column in Snowflake
name	sysname	Object name.	Yes
object_id	int	Object identification number. Is unique within a database.	No
principal_id	int	ID of the individual owner, if different from the schema owner.	No
schema_id	int	ID of the schema that the object is contained in.	No
parent_object_id	int	ID of the object to which this object belongs.	No
type	char(2)	Object type	Yes
type_desc	nvarchar(60)	Description of the object type	Yes
create_date	datetime	Date the object was created.	Yes
modify_date	datetime	Date the object was last modified by using an ALTER statement.	Yes
is_ms_shipped	bit	Object is created by an internal SQL Server component.	No
is_published	bit	Object is created by an internal SQL Server component.	No
is_schema_published	bit	Only the schema of the object is published.	No

Notice that, in this case, for the sys.foreign_keys, there is no equivalence in Snowflake. But, the equivalence is made under the columns inherited from sys.objects.

Applicable column equivalence

SQLServer	Snowflake	Limitations	Applicable
name	CONSTRAINT_NAME	Names auto-generated by the database may be reviewed to the target Snowflake auto-generated name,	Yes
type	CONSTRAINT_TYPE	The type column has a variety of options. But, in this case, the support is only for the letter 'F' which represents the foreign keys.	No. Because of the extra validation to determine the foreign keys from all table constraints, it is not applicable.
type_desc	CONSTRAINT_TYPE	No limitions found.	No. Because of the extra validation to determine the foreign keys from all table constraints, it is not applicable.
create_date	CREATED	Data type differences.	Yes
modify_date	LAST_ALTERED	Data type differences.	Yes
parent_object_id	CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAME	Columns are generated only for the cases that use the OBJECT_ID() function and, the name has a valid pattern.	Yes

Syntax in SQL Server

SELECT ('column_name' | * )
FROM sys.foreign_keys;

Syntax in Snowflake

SELECT ('column_name' | * )
FROM information_schema.table_constraints 
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';

Since the equivalence for the system foreign keys is the catalog view in Snowflake for in ormation_schema.table_constraints, it is necessary to define the type of the constraint in an additional 'WHERE' clause to identify foreign key constraints from other constraints.

Sample Source Patterns

SQL Server

IN -> SqlServer_01.sql

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT FK_Name_Test FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);


INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (1, 'John', 'Doe', '[e-mail]'),
    (2, 'Jane', 'Smith', '[e-mail]');

INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
    (101, 1, '2023-09-01', 100.50),
    (102, 1, '2023-09-02', 75.25),
    (103, 2, '2023-09-03', 50.00);

Snowflake

OUT -> SqlServer_01.sql

CREATE OR REPLACE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)