英文:
How to retrieve tables in a database ordered by tables with primary key constraints first and then the tables with foreign key constraints
问题
以下是翻译好的部分：
我正在在SQL Server上运行以下查询，以检索数据库中的表列表。虽然查询列出了表，但我正在运行一个管道，该管道将数据写入这些表，但在尝试写入外键表时失败，因为它尝试在填充主键约束表之前写入外键表。这是一个截断/加载操作。如何修改查询以使表按照具有主键约束的表首先排序，然后在具有外键约束的表之后排序。
SELECT '[ ' + TABLE_SCHEMA + '].[ ' + TABLE_NAME + ']' AS MyTableWithSchema,
TABLE_SCHEMA AS MySchema,
TABLE_NAME AS MyTable
FROM qlm.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'sysdiagrams'
ORDER BY TABLE_NAME ASC
我尝试了上面的查询，但对我的用例仍不起作用。
英文:
I am running the query below on SQL Server to retrieve a list of tables from a database. While the query lists the tables, I am running a pipeline which writes to the tables but fails on some tables as it tries to write to the foreign key tables without filling the primary key constraint tables first. This is a trunc/load operation. How can I modify the query so that it orders the tables by the tables with the primary key constraints first, and then the tables with the foreign key constraints after.
SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS MyTableWithSchema
,TABLE_SCHEMA AS MySchema
,TABLE_NAME AS MyTable
FROM qlm.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME != 'sysdiagrams'
ORDER BY TABLE_NAME ASC
I tried the query above but again this does not work for my use case.
答案1
得分: 1
你可以在订单案例中添加更多类型，以对其他 Constraint_Type 进行排序
SELECT
'[' + tab.TABLE_SCHEMA + '].[' + tab.TABLE_NAME + ']' AS MyTableWithSchema,
tab.TABLE_SCHEMA AS MySchema,
tab.TABLE_NAME AS MyTable
FROM INFORMATION_SCHEMA.TABLES tab
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab_con ON tab.TABLE_NAME = tab_con.TABLE_NAME
WHERE TABLE_TYPE = 'BASE TABLE'
AND tab.TABLE_NAME != 'sysdiagrams'
ORDER BY tab.TABLE_NAME ASC,
CASE WHEN Tab_con.Constraint_Type = 'PRIMARY KEY' THEN 1 ELSE 0 END DESC;
英文:
You can add more types to the order case, to sort other Constraint_Type
SELECT
'[' + tab.TABLE_SCHEMA + '].[' + tab.TABLE_NAME + ']' AS MyTableWithSchema
,tab.TABLE_SCHEMA AS MySchema
,tab.TABLE_NAME AS MyTable
FROM INFORMATION_SCHEMA.TABLES tab
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab_con ON tab.TABLE_NAME = tab_con.TABLE_NAME
WHERE TABLE_TYPE = 'BASE TABLE'
AND tab.TABLE_NAME != 'sysdiagrams'
ORDER BY tab.TABLE_NAME ASC,
CASE WHEN Tab_con.Constraint_Type = 'PRIMARY KEY' THEN 1 ELSE 0 END DESC;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论