Workload Generator
Workload Generator
tSql gist to generate all possible queries based on foreign key constraints.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ;WITH EdgeCTE | |
| AS | |
| ( | |
| SELECT parent_object_id Object1, | |
| referenced_object_id Object2, | |
| '[' + OBJECT_SCHEMA_NAME(FK.parent_object_id) + '].[' + OBJECT_NAME(FK.parent_object_id) + '] [T#L#1]' Table1, | |
| '[' + OBJECT_SCHEMA_NAME(FK.referenced_object_id) + '].[' + OBJECT_NAME(FK.referenced_object_id) + '] [T#L#2]' Table2, | |
| (SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY FKS.constraint_column_id) > 1 THEN ' AND ' ELSE '' END + | |
| '[T#L#1].[' + C1.name + '] = ' + | |
| '[T#L#2].[' + C2.name + ']' [text()] | |
| FROM sys.foreign_key_columns FKS | |
| INNER JOIN sys.columns C1 ON FKS.parent_object_id = C1.object_id AND FKS.parent_column_id = C1.column_id | |
| INNER JOIN sys.columns C2 ON FKS.referenced_object_id = C2.object_id AND FKS.referenced_column_id = C2.column_id | |
| WHERE FKS.parent_object_id = FK.parent_object_id | |
| AND FKS.referenced_object_id = FK.referenced_object_id | |
| ORDER BY FKS.constraint_column_id | |
| FOR XML PATH('')) OnList, | |
| COUNT(*) ColumnCnt | |
| FROM sys.foreign_key_columns FK | |
| GROUP BY FK.parent_object_id, | |
| FK.referenced_object_id | |
| ), | |
| RecursionCTE | |
| AS | |
| ( | |
| SELECT Object1, | |
| Object2, | |
| Table1, | |
| Table2, | |
| 1 Level, | |
| CAST(REPLACE(CONCAT(CHAR(13), CHAR(10), ' FROM ', Table1, CHAR(13), CHAR(10), ' INNER JOIN ', Table2, ' ON ', OnList), '#L#', '1') AS VARCHAR(MAX)) Stmt | |
| FROM EdgeCTE | |
| UNION ALL | |
| SELECT E.Object1, | |
| E.Object2, | |
| E.Table1, | |
| E.Table2, | |
| Level + 1, | |
| CAST(REPLACE(CONCAT(R.Stmt, CHAR(13), CHAR(10), ' INNER JOIN ', E.Table2, ' ON ', REPLACE(E.OnList, '#L#1', CAST(R.Level AS VARCHAR(10)) + '2')), '#L#', CAST(R.Level + 1 AS VARCHAR(10))) AS VARCHAR(MAX)) | |
| FROM EdgeCTE E | |
| INNER JOIN RecursionCTE R ON R.Object2 = E.Object1 | |
| WHERE E.Object1 != E.Object2 | |
| ) | |
| SELECT Level, | |
| CAST(CONCAT('SELECT COUNT(*) ', Stmt) AS XML), | |
| 'SELECT COUNT(*) ' + Stmt + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) | |
| FROM RecursionCTE |
