SnowConvert : Tables du système Transact¶
Tables du système¶
Transact-SQL | Snowflake SQL | Notes | |
---|---|---|---|
SYS.ALL_VIEWS | INFORMATION_SCHEMA.VIEWS | ||
SYS.ALL_COLUMNS | INFORMATION_SCHEMA.COLUMNS | ||
SYS.COLUMNS | INFORMATION_SCHEMA.COLUMNS | ||
SYS.OBJECTS | INFORMATION_SCHEMA.OBJECT_PRIVILEGES | ||
SYS.PROCEDURES | INFORMATION_SCHEMA.PROCEDURES | ||
SYS.SEQUENCES | INFORMATION_SCHEMA.SEQUENCES | ||
SYS.ALL_OBJECTS | INFORMATION_SCHEMA.OBJECT_PRIVILEGES | ||
ALL_PARAMETERS | Not supported | ||
SYS.ALL_SQL_MODULES | Not supported | ||
SYS.ALLOCATION_UNITS | Not supported | ||
SYS.ASSEMBLY_MODULES | Not supported | ||
SYS.CHECK_CONSTRAINTS | Not supported | ||
SYS.COLUMN_STORE_DICTIONARIES | Not supported | ||
SYS.COLUMN_STORE_ROW_GROUPS | Not supported | ||
SYS.COLUMN_STORE_SEGMENTS | Not supported | ||
SYS.COMPUTED_COLUMNS | Not supported | ||
SYS.DEFAULT_CONSTRAINTS | Not supported | ||
SYS.EVENTS | Not supported | ||
SYS.EVENT_NOTIFICATIONS | Not supported | ||
SYS.EVENT_NOTIFICATION_EVENT_TYPES | Not supported | ||
SYS.EXTENDED_PROCEDURES | Not supported | ||
SYS.EXTERNAL_LANGUAGE_FILES | Not supported | ||
SYS.EXTERNAL_LANGUAGES | Not supported | ||
SYS.EXTERNAL_LIBRARIES | Not supported | ||
SYS.EXTERNAL_LIBRARY_FILES | Not supported | ||
SYS.FOREIGN_KEYS | INFORMATION_SCHEMA.TABLE_CONSTRAINTS | ||
SYS.FOREIGN_KEY_COLUMNS | Not supported | ||
SYS.FUNCTION_ORDER_COLUMNS | Not supported | ||
SYS.HASH_INDEXES | Not supported | ||
SYS.INDEXES | Not supported | ||
SYS.INDEX_COLUMNS | Not supported | ||
SYS.INDEX_RESUMABLE_OPERATIONS | Not supported | ||
SYS.INTERNAL_PARTITIONS | Not supported | ||
SYS.INTERNAL_TABLES | Not supported | ||
SYS.KEY_CONSTRAINTS | Not supported | ||
SYS.MASKED_COLUMNS | Not supported | ||
SYS.MEMORY_OPTIMIZED_TABLES_INTERNAL_ATTRIBUTES | Not supported | ||
SYS.MODULE_ASSEMBLY_USAGES | Not supported | ||
SYS.NUMBERED_PROCEDURES | Not supported | ||
SYS.NUMBERED_PROCEDURE_PARAMETERS | Not supported | ||
SYS.PARAMETERS | Not supported | ||
SYS.PARTITIONS | Not supported | ||
SYS.PERIODS | Not supported | ||
SYS.SERVER_ASSEMBLY_MODULES | Not supported | ||
SYS.SERVER_EVENTS | Not supported | ||
SYS.SERVER_EVENTT_NOTIFICATIONS | Not supported | ||
SYS.SERVER_SQL_MODULE | Not supported | ||
SYS.SERVER_TRIGGERS | Not supported | ||
SYS._SERVER_TRIGGER_EVENTS | Not supported | ||
SYS.SQL_DEPENDENCIES | Not supported | ||
SYS.SQL_EXPRESSION_DEPENDENCIES | Not supported | ||
SYS.SQL_MODULES | Not supported | ||
SYS.STATS | Not supported | ||
SYS.STATS_COLUMNS | Not supported | ||
SYS.SYNONYMS | Not supported | ||
SYS.SYSTEM_COLUMNS | Not supported | ||
SYS.SYSTEM_OBJECTS | Not supported | ||
SYS.SYSTEM_PARAMETERS | Not 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 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 |
Les colonnes héritées de sys.objects sont les suivantes :
Pour plus d’informations, consultez la documentation sys.objects.
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 |
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
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 |
Syntaxe dans SQL Server
SELECT ('column_name' | * )
FROM sys.foreign_keys;
Syntaxe dans Snowflake
SELECT ('column_name' | * )
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';
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);
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¶
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¶
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 |
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';
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¶
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 |
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')
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¶
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';
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 |
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';
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¶
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¶
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';
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¶
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¶
SELECT *
FROM sys.foreign_keys
WHERE modify_date = CURRENT_TIMESTAMP;
The query produced no results.
Snowflake¶
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¶
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;
The foreign keys altered in the last 30 days.
Snowflake¶
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¶
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¶
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.
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;
name |
---|
FK_Name_Test |
Snowflake¶
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¶
SELECT
fk.name
FROM sys.foreign_keys AS fk;
name |
---|
FK_Name_Test |
Snowflake¶
SELECT
fk.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS fk
WHERE
CONSTRAINT_TYPE = 'FOREIGN KEY';
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;
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.¶
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¶
SSC-EWI-0073 : En attente de l’examen de l’équivalence fonctionnelle.