SnowConvert - Translation Spec
  • For Teradata
  • Translation Reference
    • Teradata
      • Scripts to Snowflake SQL Translation Reference
        • COMMON STATEMENTS
          • ERROR HANDLING
          • EXIT or QUIT
          • GOTO
          • IF... THEN...
        • BTEQ
        • MLOAD
          • Import
      • Scripts To Python Translation Reference
        • BTEQ
          • REPEAT
          • USING REQUEST MODIFIER
        • FLOAD
          • BEGIN LOADING
        • MLOAD
          • BEGIN MLOAD
        • TPT
        • SnowConvert Scripts Helpers
          • Technical Documentation
      • SQL Translation Reference
        • Built-in Functions
          • CURRENT_TIMESTAMP
          • Cast to DATE using { }
          • Cast to INTERVAL datatype
          • COALESCE
          • DAYNUMBER_OF_MONTH
          • FROM_BYTES
          • GETQUERYBANDVALUE
          • JSON_CHECK
          • JSON_EXTRACT
          • JSON_TABLE
          • NEW JSON
          • NVP
          • OVERLAPS
          • PIVOT
          • P_INTERSECT
          • RANK
          • Regex functions
          • STRTOK_SPLIT_TO_TABLE
          • SUBSTRING
          • TD_UNPIVOT
          • TO_CHAR
          • XMLAGG
        • Database DBC
        • Data Types
        • DDL
          • Tables
            • WITH DEFAULT
          • Index
          • Views
        • DML
          • Delete Statement
          • Insert Statement
            • LOGGING ERRORS
          • Select Statement
            • Expand On Clause
            • Normalize
            • Reset When
          • Set Operators
          • Update Statement
          • With Modifier
        • SnowConvert Procedures Helpers
          • Cursor Helper
          • Exec Helper
          • Functional Equivalence Helpers
          • Into Helper
      • Teradata to JavaScript Translation Reference
        • GET DIAGNOSTICS EXCEPTION
        • Macros
        • Procedures
      • Teradata to Snowflake Scripting Translation Reference
        • ABORT and ROLLBACK
        • BEGIN END
        • CASE
        • CREATE MACRO
        • CREATE PROCEDURE
        • CURSOR
        • DECLARE
        • DECLARE CONDITION HANDLER
        • DML and DDL Objects
        • EXCEPTION HANDLERS
        • EXECUTE IMMEDIATE
        • EXECUTE/EXEC
        • FUNCTION OPTIONS OR DATA ACCESS
        • GET DIAGNOSTICS EXCEPTION
        • IF
        • LOCKING FOR ACCESS
        • LOOP
        • OUTPUT PARAMETERS
        • PREPARE
        • REPEAT
        • SET
        • SYSTEM_DEFINED
        • WHILE
    • Oracle
      • Sample data
      • Basic Elements of Oracle SQL
        • Data Types
          • Oracle Built-in Data Types
            • Character Data Types
              • CHAR Data type
              • NCHAR Data Type
              • VARCHAR2 Data Type
              • VARCHAR Data Type
              • NVARCHAR2 Data Type
            • Numeric Data Types
              • NUMBER Data Type
              • FLOAT Data Type
              • Floating-Point Numbers
                • BINARY_FLOAT
                • BINARY_DOUBLE
            • LONG Data Type
            • Datetime and Interval Data Types
              • DATE Data Type
              • TIMESTAMP Data Type
              • TIMESTAMP WITH TIME ZONE Data Type
              • TIMESTAMP WITH LOCAL TIME ZONE Data Type
              • INTERVAL YEAR TO MONTH Data Type
              • INTERVAL DAY TO SECOND Data Type
              • Datetime Arithmetic
                • Interval UDFs vs Snowflake native interval operation
            • LOB Data Types
              • BFILE Data Type
              • BLOB Data Type
              • CLOB Data Type
              • NCLOB Data type
            • JSON Data Type
            • Extended Data Types
            • RAW and LONG RAW Data types
            • PL SQL Data Types
              • PLS_INTEGER Data Type
              • BINARY_INTEGER Data Type
          • Rowid Data Type
            • ROWID DataType
            • UROWID Data Type
          • ANSI Data Types
          • User-Defined Types
            • REF Data Types
          • Any Types
            • ANYTYPE
            • ANYDATA
            • ANYDATASET
          • XML Types
            • XMLType
            • URI Data Types
              • HTTPURIType
              • XDBURIType
              • DBURIType
            • URIFactory Package
          • Spatial Types
            • SDO_GEOMETRY
            • SDO_TOPO_GEOMETRY
            • SDO_GEORASTER
        • Literals
          • Interval Literal
          • Interval Type and Date Type
          • Text literals
      • Pseudocolumns
        • ROWID
        • ROWNUM
      • Built-in functions
        • SnowConvert Custom UDFs
          • BFILENAME UDF
          • DATE_TO_JULIANDAYS_UDF
          • DATEADD UDF
          • DATEDIFF UDF
          • INTERVAL UDFs
            • DATEADD UDF INTERVAL
            • DATEDIFF UDF INTERVAL
          • CAST_DATE UDF
          • JSON_VALUE UDF
          • JULIAN TO GREGORIAN DATE UDF
          • MONTHS BETWEEN UDF [DEPRECATED]
          • REGEXP LIKE UDF
          • TIMESTAMP DIFF UDF
          • TRUNC (date) UDF
          • TRUNC (number) UDF
        • TO_NUMBER
        • NLSSORT
      • Built-In packages
        • DBMS_LOB
          • SUBSTR Function
        • DBMS_RANDOM
          • VALUE functions
        • DBMS_OUTPUT
          • PUT_LINE procedure
        • UTL_FILE
          • FOPEN procedure
          • PUT_LINE procedure
          • FCLOSE procedure
      • SQL Queries and Subqueries
        • Select
          • Select Flashback Query
        • Joins
          • Equijoin
          • Band Join
          • Self Join
          • Cartesian Products
          • Inner Join
          • Outer Join
          • Antijoin
          • Semijoin
      • SQL Statements
        • Alter Session
        • Alter Table
        • Create Materialized Views
        • Create Database Link
        • Create Index
        • Create Sequence
        • Create Synonym
        • Create Table
        • Create Type
          • Object Type Definition
          • Subtype Definition
          • Array Type Definition
          • Nested Table Type Definition
          • Member Function Definitions
        • Create View
        • Drop Table
      • PL/SQL to Snowflake Scripting
        • ASSIGNMENT STATEMENT
        • CALL
        • CASE
        • COLLECTIONS AND RECORDS
          • Associative Array Type Definition
          • Varray Type Definition
          • Nested Table Array Type Definition
          • Collection Methods
          • Collection Bulk Operations
            • WITH, SELECT, and BULK COLLECT INTO statements
          • Record Type Definition
        • COMPOUND STATEMENTS
        • CONTINUE
        • CREATE PROCEDURE
        • CURSOR
          • PARAMETRIZED CURSOR
          • CURSOR DECLARATION
          • Workaround for cursors using parameters or procedure variables
          • Cursor Variables
        • DECLARE
        • DEFAULT PARAMETERS
        • DML STATEMENTS
          • INSERT Statement Extension
          • MERGE Statement
          • SELECT INTO Statement
          • Work around to simulate the use of Records
        • EXIT
        • EXPRESSIONS
        • EXECUTE IMMEDIATE
        • FORALL
        • FOR LOOP
        • HELPERS
          • Bulk Cursor Helpers
        • IF
        • IS EMPTY
        • LOCK TABLE
        • LOG ERROR
        • LOOP
        • OUTPUT PARAMETERS
        • PACKAGES
          • DECLARATION
          • BODY
          • VARIABLES
          • Constants
        • PROCEDURE CALL
        • RAISE
        • RAISE_APPICATION_ERROR
        • UDF CALL
        • WHILE
      • PL/SQL to Javascript
        • Procedures
        • User defined functions
        • Packages
        • Helpers
          • EXEC Helper
          • Cursor Helper
          • Raise Helper
          • ROWTYPE Helper
          • Between operator helper
          • Like operator Helper
          • IS NULL Helper
          • Concat Value Helper
          • Package variables helper
          • Implicit Cursor attribute helper
        • Declarations
        • Control Statements
        • Conditional Compilation
        • Collections & Records
        • DDL - DML Statements
        • SQL Language Elements
        • Expressions and operators
        • Synonyms
        • Triggers
        • TYPE attribute
      • SQL*Plus
        • Archive Log
        • Attribute
        • Break
        • Btitle
        • Change
        • Column
        • Define
        • Host
        • Prompt
        • Remark
        • Set
        • Spool
        • Start
        • Whenever oserror
        • Whenever sqlerror
        • Show
        • Append
        • Accept
      • Wrapped objects
    • SQLServer
      • General Language Elements
        • COMPUTED COLUMN
        • EXECUTE
          • System Store Procedures
            • SP_RENAME
        • Collate
        • USE
        • OUTER APPLY
      • DDLs
        • Tables
        • Index
        • Views
        • Procedures
        • Functions
      • DMLs
        • Set Operators
        • Between
        • Update
        • Select
        • Insert
        • Delete
        • Merge
        • Exists
        • IN
        • Truncate
        • Drop
        • Bulk Insert
        • Common Table Expression (CTE)
        • Drops
      • Data Types
      • Statements
        • ALTER
          • TABLE
            • ADD
              • COLUMN DEFINITION
                • COLUMN CONSTRAINT
                  • FOREIGN KEY
                  • PRIMARY KEY / UNIQUE
                  • CHECK
              • TABLE CONSTRAINT
                • FOREIGN KEY
                • PRIMARY KEY
                • CHECK CONSTRAINT
                • CHECK
                • CONNECTION
                • DEFAULT
                • ON PARTITION
        • CREATE
          • FUNCTION
            • SCALAR
            • INLINE TABLE-VALUED
            • MULTI-STATEMENT TABLE-VALUED
      • Built-in functions
        • SnowConvert custom UDFs
          • OPENXML UDF
          • STR UDF
          • SWITCHOFFSET_UDF
        • Aggregate functions
          • COUNT
          • COUNT_BIG
          • SUM
        • Analytic Functions
          • LAG
        • Conversion functions
          • CONVERT
          • TRY_CONVERT
        • Data Type functions
          • DATALENGTH
        • Date & Time functions
          • AT TIME ZONE
          • SWITCHOFFSET
          • DATEADD
          • DATEDIFF
          • DATEPART
          • DATEFROMPARTS
          • DATENAME
          • DAY
          • EOMONTH
          • GETDATE
          • MONTH
          • SYSDATETIME
          • SYSUTCDATETIME
          • YEAR
        • Logical functions
          • IIF
        • Mathematical functions
          • ABS
          • ACOS
            • ACOS in JS
          • ASIN
            • ASIN in JS
          • ATAN
            • ATAN in JS
          • ATN2
            • ATAN2 in JS
          • AVG
          • CEILING
          • COS
            • COS in JS
          • COT
            • COT in JS
          • DEGREES
            • DEGREES in JS
          • EXP
            • EXP in JS
          • FLOOR
          • LOG
            • LOG in JS
          • LOG10
            • LOG10 in JS
          • PI
            • PI in JS
          • POWER
            • POW in JS
          • SQUARE
          • STDEV
          • STDEVP
          • VAR
          • POWER
          • RADIANS
            • RADIANS in JS
          • ROUND
          • SQRT
        • Metadata functions
          • DB_NAME
          • OBJECT_ID
        • Ranking functions
          • DENSE_RANK
          • RANK
          • ROW_NUMBER
        • String functions
          • ASCII
            • ASCII in JS
          • CHAR
          • CHARINDEX
          • COALESCE
          • CONCAT
          • CONCAT_WS
            • Join in JS
          • DIFFERENCE
            • DIFFERENCE in JS
          • FORMAT
            • FORMAT in JS
          • LEFT
          • LEN
          • LOWER
          • LTRIM
            • LTRIM in JS
          • NCHAR
          • PATINDEX
            • search in JS
          • QUOTENAME
            • QUOTENAME in JS
          • REPLACE
          • REPLICATE
          • REVERSE
            • reverse in JS
          • RIGHT
          • RTRIM
          • SOUNDEX
            • SOUNDEX in JS
          • SPACE
          • STR
            • STR in JS
          • STRING_ESCAPE
            • stringify in JS
          • SUBSTRING
          • TRIM
            • trim in JS
          • UPPER
        • System functions
          • FORMATMESSAGE
            • FORMATMESSAGE_UDF
          • ISNULL
          • NEWID
          • NULLIF
        • XML Functions
          • Value
          • Query
      • Built-in procedures
        • Custom User Defined Procedures
          • SP_ADDEXTENDEDPROPERTY_UDP
      • Snowflake Scripting
        • CREATE PROCEDURE
        • CASE
        • CURSOR
        • DECLARE
        • EXECUTE
        • IF
        • SET
        • DMLs
        • CALL
        • WHILE
        • DROPs
        • BEGIN and COMMIT Transaction
        • OUTPUT PARAMETERS
        • LABEL and GOTO
        • SELECT
        • TEXTIMAGE_ON
        • TRY CATCH
      • System Tables
        • SYS.FOREIGN_KEYS
      • Queries
        • TOP
Powered by GitBook
On this page
  • Description
  • Applicable column equivalence
  • Sample Source Patterns
  • 1. Simple Select Case
  • 2. Name Column Case
  • 3. Parent Object ID Case
  • 4. Type Column Case
  • 5. Type Desc Column Case
  • 6. Modify Date Column Simple Case
  • 7. Modify Date Column with DATEDIFF() Case
  • 8. Create Date Column Case
  • 9. Selected Columns Single Name Case
  • 10. Selected Columns Qualified Name Case
  • Known Issues
  • Related EWIs
  1. Translation Reference
  2. SQLServer
  3. System Tables

SYS.FOREIGN_KEYS

PreviousSystem TablesNextQueries

Last updated 1 year ago

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

Description

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

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>

-

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

No

delete_referential_action_desc

nvarchar(60)

No

update_referential_action

tinyint

No

update_referential_action_desc

nvarchar(60)

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:

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

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', 'john.doe@example.com'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com');

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);
OUT -> SqlServer_01.sql
CREATE OR REPLACE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

CREATE OR REPLACE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
       CONSTRAINT FK_Name_Test FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
   )
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (1, 'John', 'Doe', 'john.doe@example.com'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com');

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);

1. Simple Select Case

SQL Server

IN -> SqlServer_02.sql
SELECT *
FROM sys.foreign_keys;
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

FK_Name_Test

1719677174

NULL

1

F

FOREIGN_KEY_CONSTRAINT

2023-09-11 22:20:04.160

2023-09-11 22:20:04.160

1687677060

false

true

false

1655676946

1

false

false

0

NO_ACTION

0

NO_ACTION

true

Snowflake

OUT -> SqlServer_02.sql
SELECT *
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'FOREIGN KEY';
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
IS_DEFERRABLE
INITIALLY_DEFERRED
ENFORCED
COMMENT
CREATED
LAST_ALTERED
RELY

DBTEST

PUBLIC

FK_Name_Test

DATETEST

PUBLIC

ORDERS

FOREIGN KEY

NO

YES

NO

null

2023-09-11 15:23:51.969 -0700

2023-09-11 15:23:52.097 -0700

NO

Results differ due to the differences in column objects and missing equivalence. The result may be checked.

2. Name Column Case

SQL Server

IN -> SqlServer_03.sql
SELECT * FROM sys.foreign_keys WHERE name = 'FK_Name_Test';
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

FK_Name_Test

1719677174

NULL

1

F

FOREIGN_KEY_CONSTRAINT

2023-09-11 22:20:04.160

2023-09-11 22:20:04.160

1687677060

false

true

false

1655676946

1

false

false

0

NO_ACTION

0

NO_ACTION

true

Snowflake

OUT -> SqlServer_03.sql
SELECT * FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_NAME = 'FK_NAME_TEST'
AND CONSTRAINT_TYPE = 'FOREIGN KEY';
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
IS_DEFERRABLE
INITIALLY_DEFERRED
ENFORCED
COMMENT
CREATED
LAST_ALTERED
RELY

DBTEST

PUBLIC

FK_Name_Test

DATETEST

PUBLIC

ORDERS

FOREIGN KEY

NO

YES

NO

null

2023-09-11 15:23:51.969 -0700

2023-09-11 15:23:52.097 -0700

NO

3. Parent Object ID Case

In this example, a database and schema were created to exemplify the processing of the names to create different and equivalent columns.

SQL Server

IN -> SqlServer_04.sql
use database_name_test
create schema schema_name_test

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

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

INSERT INTO schema_name_test.Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (1, 'John', 'Doe', 'john.doe@example.com'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO schema_name_test.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);

SELECT * FROM sys.foreign_keys WHERE name = 'FK_Name_Test' AND parent_object_id = OBJECT_ID(N'database_name_test.schema_name_test.Orders')
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

FK_Name_Test

1719677174

NULL

1

F

FOREIGN_KEY_CONSTRAINT

2023-09-11 22:20:04.160

2023-09-11 22:20:04.160

1687677060

false

true

false

1655676946

1

false

false

0

NO_ACTION

0

NO_ACTION

true

Snowflake

OUT -> SqlServer_04.sql
USE DATABASE database_name_test;

!!!RESOLVE EWI!!! /*** SSC-EWI-0073 - PENDING FUNCTIONAL EQUIVALENCE REVIEW FOR 'CreateSchema' NODE ***/!!!
create schema schema_name_test

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

CREATE OR REPLACE TABLE schema_name_test.Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2),
    CONSTRAINT FK_Name_Test FOREIGN KEY (CustomerID) REFERENCES schema_name_test.Customers (CustomerID)
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
;

INSERT INTO schema_name_test.Customers (CustomerID, FirstName, LastName, Email)
VALUES
    (1, 'John', 'Doe', 'john.doe@example.com'),
    (2, 'Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO schema_name_test.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);

SELECT * FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    CONSTRAINT_NAME = 'FK_NAME_TEST'
    AND CONSTRAINT_CATALOG = 'DATABASE_NAME_TEST'
    AND CONSTRAINT_SCHEMA = 'SCHEMA_NAME_TEST'
    AND TABLE_NAME = 'ORDERS'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY';
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
IS_DEFERRABLE
INITIALLY_DEFERRED
ENFORCED
COMMENT
CREATED
LAST_ALTERED
RELY

DATABASE_NAME_TEST

SCHEMA_NAME_TEST

FK_Name_Test

DATABASE_NAME_TEST

SCHEMA_NAME_TEST

ORDERS

FOREIGN KEY

NO

YES

NO

null

2023-09-11 15:23:51.969 -0700

2023-09-11 15:23:52.097 -0700

NO

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.

SQL Server

IN -> SqlServer_05.sql
SELECT * FROM sys.foreign_keys WHERE type = 'F';
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

FK_Name_Test

1719677174

NULL

3

F

FOREIGN_KEY_CONSTRAINT

2023-09-11 22:20:04.160

2023-09-11 22:20:04.160

1687677060

false

true

false

1655676946

1

false

false

0

NO_ACTION

0

NO_ACTION

true

Snowflake

OUT -> SqlServer_05.sql
SELECT * FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    type = 'F' AND CONSTRAINT_TYPE = 'FOREIGN KEY';
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
IS_DEFERRABLE
INITIALLY_DEFERRED
ENFORCED
COMMENT
CREATED
LAST_ALTERED
RELY

DBTEST

PUBLIC

FK_Name_Test

DATETEST

PUBLIC

ORDERS

FOREIGN KEY

NO

YES

NO

null

2023-09-11 15:23:51.969 -0700

2023-09-11 15:23:52.097 -0700

NO

5. Type Desc Column Case

The 'type_desc' column is removed due to the validation at the ending to specify the foreign key from all the table constraints.

SQL Server

IN -> SqlServer_06.sql
SELECT
    * 
FROM
    sys.foreign_keys 
WHERE 
    type_desc = 'FOREIGN_KEY_CONSTRAINT';
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

FK_Name_Test

1719677174

NULL

3

F

FOREIGN_KEY_CONSTRAINT

2023-09-11 22:20:04.160

2023-09-11 22:20:04.160

1687677060

false

true

false

1655676946

1

false

false

0

NO_ACTION

0

NO_ACTION

true

Snowflake

OUT -> SqlServer_06.sql
SELECT
    *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    type_desc = 'FOREIGN_KEY_CONSTRAINT' AND CONSTRAINT_TYPE = 'FOREIGN KEY';
CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
IS_DEFERRABLE
INITIALLY_DEFERRED
ENFORCED
COMMENT
CREATED
LAST_ALTERED
RELY

DBTEST

PUBLIC

FK_Name_Test

DATETEST

PUBLIC

ORDERS

FOREIGN KEY

NO

YES

NO

null

2023-09-11 15:23:51.969 -0700

2023-09-11 15:23:52.097 -0700

NO

6. Modify Date Column Simple Case

SQL Server

IN -> SqlServer_07.sql
SELECT *
FROM sys.foreign_keys
WHERE modify_date = CURRENT_TIMESTAMP;
The query produced no results.

Snowflake

OUT -> SqlServer_07.sql
SELECT *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    LAST_ALTERED = CURRENT_TIMESTAMP()
    AND CONSTRAINT_TYPE = 'FOREIGN KEY';
The query produced no results.

7. Modify Date Column with DATEDIFF() Case

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.

SQL Server

IN -> SqlServer_08.sql
SELECT *
FROM sys.foreign_keys
WHERE DATEDIFF(DAY, modify_date, GETDATE()) <= 30;
The foreign keys altered in the last 30 days.

Snowflake

OUT -> SqlServer_08.sql
SELECT *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    DATEDIFF(DAY, LAST_ALTERED, CURRENT_TIMESTAMP() :: TIMESTAMP) <= 30
    AND CONSTRAINT_TYPE = 'FOREIGN KEY';
The foreign keys altered in the last 30 days.

8. Create Date Column Case

SQL Server

IN -> SqlServer_09.sql
SELECT *
FROM sys.foreign_keys
WHERE create_date = '2023-09-12 14:36:38.060';
The foreign keys that were created on the specified date and time.

Snowflake

OUT -> SqlServer_09.sql
SELECT *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    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.

9. Selected Columns Single Name Case

SQL Server

IN -> SqlServer_10.sql
SELECT name
FROM sys.foreign_keys;
name

FK_Name_Test

Snowflake

OUT -> SqlServer_10.sql
SELECT
    CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    CONSTRAINT_TYPE = 'FOREIGN KEY';
CONSTRAINT_NAME

FK_Name_Test

10. Selected Columns Qualified Name Case

SQL Server

IN -> SqlServer_11.sql
SELECT
    fk.name
FROM sys.foreign_keys AS fk;
name

FK_Name_Test

Snowflake

OUT -> SqlServer_11.sql
SELECT
    fk.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS fk
WHERE
    CONSTRAINT_TYPE = 'FOREIGN KEY';
CONSTRAINT_NAME

FK_Name_Test

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
SELECT name AS ForeignKeyName,
       OBJECT_NAME(parent_object_id) AS ReferencingTable,
       OBJECT_NAME(referenced_object_id) AS ReferencedTable
FROM sys.foreign_keys;
OUT -> SqlServer_12.sql
SELECT
    name AS 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 ReferencedTable
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    CONSTRAINT_TYPE = 'FOREIGN KEY';

5. SCHEMA_NAME() and TYPE_NAME() functions are also not supported yet.

7. Cases with JOIN statements are not supported.

8. Names with alias AS are not supported.

Related EWIs

For a list of columns that this view inherits, see

The referential action that was declared for this FOREIGN KEY when a delete happens. See .

Description of the referential action that was declared for this FOREIGN KEY when a delete occurs. See .

The referential action that was declared for this FOREIGN KEY when an update happens. See .

Description of the referential action that was declared for this FOREIGN KEY when an update happens. See .

For more information, review the .

To accomplish correctly the following samples (except ), it is required to run the following statements:

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.

6. Different Join statement types may be not supported if the system table is not supported. Review the supported system tables .

: Pending Functional Equivalence Review.

SQLServer Documentation
sys.objects documentation
pattern number 3
Know Issues
here
sys.objects (Transact-SQL).
SQLServer Documentation
SQLServer Documentation
SQLServer Documentation
SQLServer Documentation
SSC-EWI-0073