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
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.
If the name coming inside the OBJECT_ID() function does not have a valid pattern, it will not be converted due to name processing limitations on special characters.
Review the database that is being used in Snowflake.
4. Type Column Case
The 'F' in SQL Server means 'Foreign Key' and it is removed due to the validation at the ending to specify the foreign key from all the table constraints.
The following example shows a more complex scenario where the columns from sys.foreign_keys (inherited from sys.objects) are inside a function DATEDIFF. In this case, the argument corresponding to the applicable equivalence is changed to the corresponding column from the information.schema in Snowflake.
The foreign keys that were created on the specified date and time.
Snowflake
OUT -> SqlServer_09.sql
SELECT*FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSWHERE CREATED ='2023-09-12 14:36:38.060'AND CONSTRAINT_TYPE ='FOREIGN KEY';
The foreign keys that were created on the specified date and time.
The result may change if the creation date is specific due to the time on which the queries were executed. It is possible to execute a specified query at one time on the origin database and then execute the objects at another time in the new Snowflake queries.
SELECT fk.CONSTRAINT_NAMEFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS fkWHERE CONSTRAINT_TYPE ='FOREIGN KEY';
Known Issues
1. The 'name' column may not show a correct output if the constraint does not have a user-created name
If the referenced name is one auto-generated from the database, it would be probable to review it and use the wanted value.
2. When selecting columns, there is a limitation that depends on the applicable columns that are equivalent in Snowflake
Since the columns from sys.foreign_keys are not completely equivalent in Snowflake, some results may change due to the limitations on the equivalence.
3. The OBJECT_ID() function may have a valid pattern to be processed or the database, schema or table could not be extracted
Based on the name that receives the OBJECT_ID() function, the processing of this name will be limited and dependent on formatting.
4. Name Column With OBJECT_NAME() Function Case
Since the OBJECT_NAME() function is not supported yet, the transformations related to this function are not supported.
IN -> SqlServer_12.sql
SELECTnameAS ForeignKeyName,OBJECT_NAME(parent_object_id) AS ReferencingTable,OBJECT_NAME(referenced_object_id) AS ReferencedTableFROM sys.foreign_keys;
OUT -> SqlServer_12.sql
SELECTnameAS ForeignKeyName, !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'OBJECT_NAME' NODE ***/!!!OBJECT_NAME(parent_object_id) AS ReferencingTable, !!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'OBJECT_NAME' NODE ***/!!!OBJECT_NAME(referenced_object_id) AS ReferencedTableFROM INFORMATION_SCHEMA.TABLE_CONSTRAINTSWHERE CONSTRAINT_TYPE ='FOREIGN KEY';
5. SCHEMA_NAME() and TYPE_NAME() functions are also not supported yet.
6. Different Join statement types may be not supported if the system table is not supported. Review the supported system tables here.
To accomplish correctly the following samples (except ), it is required to run the following statements:
name
object_id
principal_id
schema_id
type
type_desc
create_date
modify_date
parent_object_id
is_ms_shipped
is_published
is_schema_published
referenced_object_id
key_index_id
is_disabled
is_not_for_replication
is_not_trusted
delete_referential_action
delete_referential_action_desc
update_referential_action
update_referential_action_desc
is_system_named
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
IS_DEFERRABLE
INITIALLY_DEFERRED
ENFORCED
COMMENT
CREATED
LAST_ALTERED
RELY
name
object_id
principal_id
schema_id
type
type_desc
create_date
modify_date
parent_object_id
is_ms_shipped
is_published
is_schema_published
referenced_object_id
key_index_id
is_disabled
is_not_for_replication
is_not_trusted
delete_referential_action
delete_referential_action_desc
update_referential_action
update_referential_action_desc
is_system_named
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
IS_DEFERRABLE
INITIALLY_DEFERRED
ENFORCED
COMMENT
CREATED
LAST_ALTERED
RELY
This translation may require verification if the constraint name is auto-generated by the database and used in the query. For more information review the section.