本文共 3221 字,大约阅读时间需要 10 分钟。
目录
在使用关系数据库的过程中,我找到了表之间的关系。目的是以正确的顺序清理表并从另一个数据库同步数据,这使我找到了一个查找表生成顺序的方案。
我们在这做什么?
IF object_id('tempdb..#tblRelation') is not null DROP TABLE #tblRelation;IF object_id('tempdb..#tblDetail') is not null DROP TABLE #tblDetail;/*table relation details*/CREATE TABLE #tblRelation (ObjectId VARCHAR(100) NOT NULL, Name VARCHAR(100) NOT NULL, [Schema] VARCHAR(100) NOT NULL, [Column] VARCHAR(100) NOT NULL, FkFromObjectId VARCHAR(100) NOT NULL, FkFromTbl VARCHAR(100) NOT NULL, FkFromSchema VARCHAR(100) NOT NULL, FkFromClm VARCHAR(100) NOT NULL);/*table generation details*/CREATE TABLE #tblDetail (Name VARCHAR(100) NOT NULL, [Schema] VARCHAR(100) NOT NULL, Generation INT NULL);
在这里,我们收集表之间的关系
INSERT INTO #tblRelation SELECT parent_object_id, OBJECT_NAME(parent_object_id), OBJECT_SCHEMA_NAME(parent_object_id), c.NAME, referenced_object_id, OBJECT_NAME(referenced_object_id), OBJECT_SCHEMA_NAME(referenced_object_id), cref.NAME FROM sys.foreign_key_columns fk INNER JOIN sys.columns c ON fk.parent_column_id = c.column_id AND fk.parent_object_id = c.object_id INNER JOIN sys.columns cref ON fk.referenced_column_id = cref.column_id AND fk.referenced_object_id = cref.object_id;
现在是时候使用表关系数据查找表生成了。
什么是第一个生成表?
/*find first generation table*/INSERT INTO #tblDetail(Name, [Schema], Generation) (SELECT TABLE_NAME, TABLE_SCHEMA, (CASE WHEN( /*if tbl has no fk, first generation tbl, 0*/ (SELECT COUNT(*) FROM #tblRelation WHERE Name = TABLE_NAME AND [Schema] = TABLE_SCHEMA) = 0 ) THEN(SELECT 0) WHEN( /*if tbl has fk, but all of them from his own columns, first generation tbl, 0*/ (SELECT COUNT(*) FROM #tblRelation WHERE Name = TABLE_NAME AND [Schema] = TABLE_SCHEMA) = (SELECT COUNT(*) FROM #tblRelation WHERE Name = TABLE_NAME AND FkFromTbl = TABLE_NAME AND [Schema] = TABLE_SCHEMA) ) THEN(SELECT 0) /*tbl has fk, from other tbl columns, NULL*/ ELSE(SELECT NULL) END) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME != 'sysdiagrams');
/*table generations*/SELECT * FROM #tblDetail ORDER BY Generation, Name, [Schema];
/*table relations*/SELECT * FROM #tblRelation ORDER BY Name, [Schema], FkFromTbl, FkFromSchema;
我们也可以找到NULL作为生成或订单值。这意味着数据库中存在循环关系,重新定义表关系至关重要。
解决方案可能因数据库版本而异。我们测试的数据库是
原文地址:
转载地址:http://oizhj.baihongyu.com/