SnowConvert: Tabelas de sistema do Transact¶
Tabelas do sistema¶
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 |
Problemas conhecidos ¶
Não foram encontrados problemas.
SYS.FOREIGN_KEYS¶
Applies to
[x] SQL Server
[x] Azure Synapse Analytics
Nota
Algumas partes do código de saída foram omitidas por motivos de clareza.
Descrição¶
Contém uma linha por objeto que é uma restrição FOREIGN KEY (Documentação do SQLServer).
As colunas para FOREIGN KEY (sys.foreign_keys) são as seguintes:
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 |
As colunas herdadas de sys.objects são as seguintes:
Para obter mais informações, consulte a documentação de 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 |
Observe que, nesse caso, para sys.foreign_keys, não há equivalência no Snowflake. Porém, a equivalência é feita nas colunas herdadas de sys.objects.
Equivalência de coluna aplicável
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 |
Sintaxe no SQL Server
SELECT ('column_name' | * )
FROM sys.foreign_keys;
Sintaxe no Snowflake
SELECT ('column_name' | * )
FROM information_schema.table_constraints
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY';
Nota
Como a equivalência para as chaves estrangeiras do sistema é a visualização do catálogo no Snowflake para in ormation_schema.table_constraints, é necessário definir o tipo da restrição em uma cláusula adicional “WHERE” para identificar as restrições de chave estrangeira de outras restrições.
Amostra de padrões da origem¶
Para realizar corretamente as amostras a seguir (exceto número de padrão 3), é necessário executar as seguintes instruções:
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 |
Aviso
Os resultados diferem devido às diferenças nos objetos da coluna e à equivalência ausente. O resultado pode ser verificado.
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 |
Aviso
Essa conversão pode exigir verificação se o nome da restrição for gerado automaticamente pelo banco de dados e usado na consulta. Para obter mais informações, consulte a seção Problemas conhecidos.
3. Parent Object ID Case¶
Neste exemplo, foram criados um banco de dados e um esquema para exemplificar o processamento dos nomes para criar colunas diferentes e equivalentes.
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 |
Aviso
Se o nome que vem dentro da função OBJECT_ID() não tiver um padrão válido, ele não será convertido devido às limitações de processamento de nomes em caracteres especiais.
Aviso
Analise o banco de dados que está sendo usado no Snowflake.
4. Type Column Case¶
O «F» no SQL Server significa «Foreign Key» e é removido devido à validação no final para especificar a chave estrangeira de todas as restrições da tabela.
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¶
A coluna «type_desc» foi removida devido à validação no final para especificar a chave estrangeira de todas as restrições da tabela.
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¶
O exemplo a seguir mostra um cenário mais complexo em que as colunas de sys.foreign_keys (herdadas de sys.objects) estão dentro de uma função DATEDIFF. Nesse caso, o argumento correspondente à equivalência aplicável é alterado para a coluna correspondente de information.schema no 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.
Aviso
O resultado pode mudar se a data de criação for específica devido ao horário em que as consultas foram executadas. É possível executar uma consulta específica em um momento no banco de dados de origem e, em seguida, executar os objetos em outro momento nas novas consultas do 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 |
Problemas conhecidos¶
1. The “name” column may not show a correct output if the constraint does not have a user-created name¶
Se o nome referenciado for gerado automaticamente a partir do banco de dados, é provável que você o revise e use o valor desejado.
2. When selecting columns, there is a limitation that depends on the applicable columns that are equivalent in Snowflake¶
Como as colunas de sys.foreign_keys não são totalmente equivalentes no Snowflake, alguns resultados podem mudar devido às limitações da equivalência.
3. The OBJECT_ID() function may have a valid pattern to be processed or the database, schema or table could not be extracted¶
Com base no nome que recebe a função OBJECT_ID(), o processamento desse nome será limitado e dependerá da formatação.
4. Name Column With OBJECT_NAME() Function Case¶
Como a função OBJECT_NAME() ainda não é compatível, as transformações relacionadas a essa função não são compatíveis.
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 relacionados¶
SSC-EWI-0073: Revisão de equivalência funcional pendente.