SnowConvert : Tables du système Transact

Tables du système

Transact-SQLSnowflake SQLNotes
SYS.ALL_VIEWSINFORMATION_SCHEMA.VIEWS
SYS.ALL_COLUMNSINFORMATION_SCHEMA.COLUMNS
SYS.COLUMNSINFORMATION_SCHEMA.COLUMNS
SYS.OBJECTSINFORMATION_SCHEMA.OBJECT_PRIVILEGES
SYS.PROCEDURESINFORMATION_SCHEMA.PROCEDURES
SYS.SEQUENCESINFORMATION_SCHEMA.SEQUENCES
SYS.ALL_OBJECTSINFORMATION_SCHEMA.OBJECT_PRIVILEGES
ALL_PARAMETERSNot supported
SYS.ALL_SQL_MODULESNot supported
SYS.ALLOCATION_UNITSNot supported
SYS.ASSEMBLY_MODULESNot supported
SYS.CHECK_CONSTRAINTSNot supported
SYS.COLUMN_STORE_DICTIONARIESNot supported
SYS.COLUMN_STORE_ROW_GROUPSNot supported
SYS.COLUMN_STORE_SEGMENTSNot supported
SYS.COMPUTED_COLUMNSNot supported
SYS.DEFAULT_CONSTRAINTSNot supported
SYS.EVENTSNot supported
SYS.EVENT_NOTIFICATIONSNot supported
SYS.EVENT_NOTIFICATION_EVENT_TYPESNot supported
SYS.EXTENDED_PROCEDURESNot supported
SYS.EXTERNAL_LANGUAGE_FILESNot supported
SYS.EXTERNAL_LANGUAGESNot supported
SYS.EXTERNAL_LIBRARIESNot supported
SYS.EXTERNAL_LIBRARY_FILESNot supported
SYS.FOREIGN_KEYSINFORMATION_SCHEMA.TABLE_CONSTRAINTS
SYS.FOREIGN_KEY_COLUMNSNot supported
SYS.FUNCTION_ORDER_COLUMNSNot supported
SYS.HASH_INDEXESNot supported
SYS.INDEXESNot supported
SYS.INDEX_COLUMNSNot supported
SYS.INDEX_RESUMABLE_OPERATIONSNot supported
SYS.INTERNAL_PARTITIONSNot supported
SYS.INTERNAL_TABLESNot supported
SYS.KEY_CONSTRAINTSNot supported
SYS.MASKED_COLUMNSNot supported
SYS.MEMORY_OPTIMIZED_TABLES_INTERNAL_ATTRIBUTESNot supported
SYS.MODULE_ASSEMBLY_USAGESNot supported
SYS.NUMBERED_PROCEDURESNot supported
SYS.NUMBERED_PROCEDURE_PARAMETERSNot supported
SYS.PARAMETERSNot supported
SYS.PARTITIONSNot supported
SYS.PERIODSNot supported
SYS.SERVER_ASSEMBLY_MODULESNot supported
SYS.SERVER_EVENTSNot supported
SYS.SERVER_EVENTT_NOTIFICATIONSNot supported
SYS.SERVER_SQL_MODULENot supported
SYS.SERVER_TRIGGERSNot supported
SYS._SERVER_TRIGGER_EVENTSNot supported
SYS.SQL_DEPENDENCIESNot supported
SYS.SQL_EXPRESSION_DEPENDENCIESNot supported
SYS.SQL_MODULESNot supported
SYS.STATSNot supported
SYS.STATS_COLUMNSNot supported
SYS.SYNONYMSNot supported
SYS.SYSTEM_COLUMNSNot supported
SYS.SYSTEM_OBJECTSNot supported
SYS.SYSTEM_PARAMETERSNot supported
SYS.SYSTEM_SQL_MODULES"Not supported

Problèmes connus

Aucun problème n’a été constaté.

SYS.FOREIGN_KEYS

Applies to
  • [x] SQL Server

  • [x] Azure Synapse Analytics

Note

Certaines parties du code de sortie sont omises pour des raisons de clarté.

Description

Contient une ligne par objet qui est une contrainte FOREIGN KEY (documentation SQLServer).

Les colonnes de FOREIGN KEY (sys.foreign_keys) sont les suivantes :

Column nameData typeDescriptionHas 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_idintID of the referenced object.No
key_index_idintID of the key index within the referenced object.No
is_disabledbitFOREIGN KEY constraint is disabled.No
is_not_for_replicationbitFOREIGN KEY constraint was created by using the NOT FOR REPLICATION option.No
is_not_trustedbitFOREIGN KEY constraint has not been verified by the system.No
delete_referential_actiontinyintThe referential action that was declared for this FOREIGN KEY when a delete happens. See SQLServer Documentation.No
delete_referential_action_descnvarchar(60)Description of the referential action that was declared for this FOREIGN KEY when a delete occurs. See SQLServer Documentation.No
update_referential_actiontinyintThe referential action that was declared for this FOREIGN KEY when an update happens. See SQLServer Documentation.No
update_referential_action_descnvarchar(60)Description of the referential action that was declared for this FOREIGN KEY when an update happens. See SQLServer Documentation.No
is_system_namedbit

1 = Name was generated by the system.

0 = Name was supplied by the user.

No

Les colonnes héritées de sys.objects sont les suivantes :

Pour plus d’informations, consultez la documentation sys.objects.

Column nameData typeDescriptionHas equivalent column in Snowflake
namesysnameObject name.Yes
object_idintObject identification number. Is unique within a database.No
principal_idintID of the individual owner, if different from the schema owner.No
schema_idintID of the schema that the object is contained in.No
parent_object_idintID of the object to which this object belongs.No
typechar(2)Object typeYes
type_descnvarchar(60)Description of the object typeYes
create_datedatetimeDate the object was created.Yes
modify_datedatetimeDate the object was last modified by using an ALTER statement.Yes
is_ms_shippedbitObject is created by an internal SQL Server component.No
is_publishedbitObject is created by an internal SQL Server component.No
is_schema_publishedbitOnly the schema of the object is published.No

Remarquez que, dans ce casse, pour les sys.foreign_keys, il n’y a pas d’équivalence dans Snowflake. Mais, l’équivalence se fait sous les colonnes héritées de sys.objects.

Équivalence des colonnes applicables

SQLServerSnowflakeLimitationsApplicable
nameCONSTRAINT_NAMENames auto-generated by the database may be reviewed to the target Snowflake auto-generated name,Yes
typeCONSTRAINT_TYPEThe 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_descCONSTRAINT_TYPENo limitions found.No. Because of the extra validation to determine the foreign keys from all table constraints, it is not applicable.
create_dateCREATEDData type differences.Yes
modify_dateLAST_ALTEREDData type differences.Yes
parent_object_idCONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, TABLE_NAMEColumns are generated only for the cases that use the OBJECT_ID() function and, the name has a valid pattern.Yes
Syntaxe dans SQL Server
 SELECT ('column_name' | * )
FROM sys.foreign_keys;
Copy
Syntaxe dans Snowflake
 SELECT ('column_name' | * )
FROM information_schema.table_constraints 
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy

Note

Comme l’équivalence pour les clés étrangères du système est la vue du catalogue dans Snowflake pour les contraintes d’information _schema.table_, il est nécessaire de définir le type de la contrainte dans une clause supplémentaire “WHERE” pour identifier les contraintes de clé étrangère par rapport aux autres contraintes.

Modèles d’échantillons de sources

Pour réaliser correctement les échantillons suivants (à l’exception du modèle numéro 3), il est nécessaire d’exécuter les instructions suivantes :

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

1. Simple Select Case

SQL Server
 SELECT *
FROM sys.foreign_keys;
Copy
nameobject_idprincipal_idschema_idtypetype_desccreate_datemodify_dateparent_object_idis_ms_shippedis_publishedis_schema_publishedreferenced_object_idkey_index_idis_disabledis_not_for_replicationis_not_trusteddelete_referential_actiondelete_referential_action_descupdate_referential_actionupdate_referential_action_descis_system_named
FK_Name_Test1719677174NULL1FFOREIGN_KEY_CONSTRAINT2023-09-11 22:20:04.1602023-09-11 22:20:04.1601687677060falsetruefalse16556769461falsefalse0NO_ACTION0NO_ACTIONtrue
Snowflake
 SELECT *
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy
CONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMETABLE_CATALOGTABLE_SCHEMATABLE_NAMECONSTRAINT_TYPEIS_DEFERRABLEINITIALLY_DEFERREDENFORCEDCOMMENTCREATEDLAST_ALTEREDRELY
DBTESTPUBLICFK_Name_TestDATETESTPUBLICORDERSFOREIGN KEYNOYESNOnull2023-09-11 15:23:51.969 -07002023-09-11 15:23:52.097 -0700NO

Avertissement

Les résultats diffèrent en raison des différences dans les objets des colonnes et de l’équivalence manquante. Le résultat peut être vérifié.

2. Name Column Case

SQL Server
 SELECT * FROM sys.foreign_keys WHERE name = 'FK_Name_Test';
Copy
nameobject_idprincipal_idschema_idtypetype_desccreate_datemodify_dateparent_object_idis_ms_shippedis_publishedis_schema_publishedreferenced_object_idkey_index_idis_disabledis_not_for_replicationis_not_trusteddelete_referential_actiondelete_referential_action_descupdate_referential_actionupdate_referential_action_descis_system_named
FK_Name_Test1719677174NULL1FFOREIGN_KEY_CONSTRAINT2023-09-11 22:20:04.1602023-09-11 22:20:04.1601687677060falsetruefalse16556769461falsefalse0NO_ACTION0NO_ACTIONtrue
Snowflake
 SELECT * FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_NAME = 'FK_NAME_TEST'
AND CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy
CONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMETABLE_CATALOGTABLE_SCHEMATABLE_NAMECONSTRAINT_TYPEIS_DEFERRABLEINITIALLY_DEFERREDENFORCEDCOMMENTCREATEDLAST_ALTEREDRELY
DBTESTPUBLICFK_Name_TestDATETESTPUBLICORDERSFOREIGN KEYNOYESNOnull2023-09-11 15:23:51.969 -07002023-09-11 15:23:52.097 -0700NO

Avertissement

Cette traduction peut nécessiter une vérification si le nom de la contrainte est généré automatiquement par la base de données et utilisé dans la requête. Pour plus d’informations, consultez la section Problèmes connus.

3. Parent Object ID Case

Dans cet exemple, une base de données et un schéma ont été créés pour illustrer le traitement des noms afin de créer des colonnes différentes et équivalentes.

SQL Server
 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')
Copy
nameobject_idprincipal_idschema_idtypetype_desccreate_datemodify_dateparent_object_idis_ms_shippedis_publishedis_schema_publishedreferenced_object_idkey_index_idis_disabledis_not_for_replicationis_not_trusteddelete_referential_actiondelete_referential_action_descupdate_referential_actionupdate_referential_action_descis_system_named
FK_Name_Test1719677174NULL1FFOREIGN_KEY_CONSTRAINT2023-09-11 22:20:04.1602023-09-11 22:20:04.1601687677060falsetruefalse16556769461falsefalse0NO_ACTION0NO_ACTIONtrue
Snowflake
 USE DATABASE database_name_test;

CREATE OR REPLACE SCHEMA schema_name_test
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"transact"}}'
;

CREATE OR REPLACE TABLE schema_name_test.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 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';
Copy
CONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMETABLE_CATALOGTABLE_SCHEMATABLE_NAMECONSTRAINT_TYPEIS_DEFERRABLEINITIALLY_DEFERREDENFORCEDCOMMENTCREATEDLAST_ALTEREDRELY
DATABASE_NAME_TESTSCHEMA_NAME_TESTFK_Name_TestDATABASE_NAME_TESTSCHEMA_NAME_TESTORDERSFOREIGN KEYNOYESNOnull2023-09-11 15:23:51.969 -07002023-09-11 15:23:52.097 -0700NO

Avertissement

Si le nom entrant dans la fonction OBJECT_ID() n’a pas de modèle valide, il ne sera pas converti en raison des limites de traitement des noms sur les caractères spéciaux.

Avertissement

Passez en revue la base de données utilisée dans Snowflake.

4. Type Column Case

Le « F » dans SQL Server signifie « Foreign Key » (clé étrangère) et il est supprimé en raison de la validation à la fin pour spécifier la clé étrangère à partir de toutes les contraintes de la table.

SQL Server
 SELECT * FROM sys.foreign_keys WHERE type = 'F';
Copy
nameobject_idprincipal_idschema_idtypetype_desccreate_datemodify_dateparent_object_idis_ms_shippedis_publishedis_schema_publishedreferenced_object_idkey_index_idis_disabledis_not_for_replicationis_not_trusteddelete_referential_actiondelete_referential_action_descupdate_referential_actionupdate_referential_action_descis_system_named
FK_Name_Test1719677174NULL3FFOREIGN_KEY_CONSTRAINT2023-09-11 22:20:04.1602023-09-11 22:20:04.1601687677060falsetruefalse16556769461falsefalse0NO_ACTION0NO_ACTIONtrue
Snowflake
 SELECT * FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    type = 'F' AND CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy
CONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMETABLE_CATALOGTABLE_SCHEMATABLE_NAMECONSTRAINT_TYPEIS_DEFERRABLEINITIALLY_DEFERREDENFORCEDCOMMENTCREATEDLAST_ALTEREDRELY
DBTESTPUBLICFK_Name_TestDATETESTPUBLICORDERSFOREIGN KEYNOYESNOnull2023-09-11 15:23:51.969 -07002023-09-11 15:23:52.097 -0700NO

5. Type Desc Column Case

La colonne « type_desc » est supprimée en raison de la validation à la fin pour spécifier la clé étrangère à partir de toutes les contraintes de la table.

SQL Server
 SELECT
    * 
FROM
    sys.foreign_keys 
WHERE 
    type_desc = 'FOREIGN_KEY_CONSTRAINT';
Copy
nameobject_idprincipal_idschema_idtypetype_desccreate_datemodify_dateparent_object_idis_ms_shippedis_publishedis_schema_publishedreferenced_object_idkey_index_idis_disabledis_not_for_replicationis_not_trusteddelete_referential_actiondelete_referential_action_descupdate_referential_actionupdate_referential_action_descis_system_named
FK_Name_Test1719677174NULL3FFOREIGN_KEY_CONSTRAINT2023-09-11 22:20:04.1602023-09-11 22:20:04.1601687677060falsetruefalse16556769461falsefalse0NO_ACTION0NO_ACTIONtrue
Snowflake
 SELECT
    *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    type_desc = 'FOREIGN_KEY_CONSTRAINT' AND CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy
CONSTRAINT_CATALOGCONSTRAINT_SCHEMACONSTRAINT_NAMETABLE_CATALOGTABLE_SCHEMATABLE_NAMECONSTRAINT_TYPEIS_DEFERRABLEINITIALLY_DEFERREDENFORCEDCOMMENTCREATEDLAST_ALTEREDRELY
DBTESTPUBLICFK_Name_TestDATETESTPUBLICORDERSFOREIGN KEYNOYESNOnull2023-09-11 15:23:51.969 -07002023-09-11 15:23:52.097 -0700NO

6. Modify Date Column Simple Case

SQL Server
 SELECT *
FROM sys.foreign_keys
WHERE modify_date = CURRENT_TIMESTAMP;
Copy
The query produced no results.

Copy
Snowflake
 SELECT *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    LAST_ALTERED = CURRENT_TIMESTAMP()
    AND CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy
The query produced no results.

Copy

7. Modify Date Column with DATEDIFF() Case

L’exemple suivant montre un scénario plus complexe dans lequel les colonnes de sys.foreign_keys (héritées de sys.objects) se trouvent à l’intérieur d’une fonction DATEDIFF. Dans ce cas, l’argument correspondant à l’équivalence applicable est remplacé par la colonne correspondante du schéma information.schema dans Snowflake.

SQL Server
 SELECT *
FROM sys.foreign_keys
WHERE DATEDIFF(DAY, modify_date, GETDATE()) <= 30;
Copy
The foreign keys altered in the last 30 days.

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

Copy

8. Create Date Column Case

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

Copy
Snowflake
 SELECT *
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    CREATED = '2023-09-12 14:36:38.060'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy
The foreign keys that were created on the specified date and time.

Copy

Avertissement

Le résultat peut changer si la date de création est spécifique en raison de l’heure à laquelle les requêtes ont été exécutées. Il est possible d’exécuter une requête spécifique à un moment donné sur la base de données d’origine, puis d’exécuter les objets à un autre moment dans les nouvelles requêtes Snowflake.

9. Selected Columns Single Name Case

SQL Server
 SELECT name
FROM sys.foreign_keys;
Copy

name

FK_Name_Test

Snowflake
 SELECT
    CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
    CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy

CONSTRAINT_NAME

FK_Name_Test

10. Selected Columns Qualified Name Case

SQL Server
 SELECT
    fk.name
FROM sys.foreign_keys AS fk;
Copy

name

FK_Name_Test

Snowflake
 SELECT
    fk.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS fk
WHERE
    CONSTRAINT_TYPE = 'FOREIGN KEY';
Copy

CONSTRAINT_NAME

FK_Name_Test

Problèmes connus

1. The “name” column may not show a correct output if the constraint does not have a user-created name

Si la référence est un nom généré automatiquement à partir de la base de données, il est probable qu’elle soit révisée et que la valeur souhaitée soit utilisée.

2. When selecting columns, there is a limitation that depends on the applicable columns that are equivalent in Snowflake

Les colonnes de sys.foreign_keys n’étant pas complètement équivalentes dans Snowflake, certains résultats peuvent changer en raison des limites d’équivalence.

3. The OBJECT_ID() function may have a valid pattern to be processed or the database, schema or table could not be extracted

En fonction du nom qui reçoit la fonction OBJECT_ID(), le traitement de ce nom sera limité et dépendra du formatage.

4. Name Column With OBJECT_NAME() Function Case

La fonction OBJECT_NAME() n’étant pas encore prise en charge, les transformations liées à cette fonction ne sont pas prises en charge.

 SELECT name AS ForeignKeyName,
       OBJECT_NAME(parent_object_id) AS ReferencingTable,
       OBJECT_NAME(referenced_object_id) AS ReferencedTable
FROM sys.foreign_keys;
Copy
 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';
Copy
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.
7. Cases with JOIN statements are not supported.
8. Names with alias AS are not supported.

EWIs connexes

  1. SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle.