Created
January 29, 2024 20:08
-
-
Save jnm2/72f8776a5335675229044ea317b58312 to your computer and use it in GitHub Desktop.
This file contains 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
declare @schemaName sysname = 'dbo' | |
declare @tableName sysname = 'ItemPrices' | |
declare @rowCondition nvarchar(max) = 'PriceCode in (''SEEDS'', ''YEAREND'')' | |
declare @builder nvarchar(max); | |
select @builder = coalesce(@builder + ' | |
', '') + 'if exists( | |
select *' + QueryWithoutSelect + ') | |
begin | |
select ' + quotename(TableName, '''') + ' as [(table)], *' + QueryWithoutSelect + ' | |
end' | |
from ( | |
select | |
TableName = parent_schemas.name + '.' + parent_tables.name, | |
QueryWithoutSelect = ' | |
from ' + parent_schemas.name + '.' + parent_tables.name + ' | |
where exists(select * from ' + referenced_schemas.name + '.' + referenced_tables.name + ' where ' + AllConstraintsJoinCondition + iif(len(@rowCondition) > 0, ' and ' + @rowCondition, '') + ')' | |
from ( | |
select | |
referenced_object_id, | |
parent_object_id, | |
iif(count(*) = 1, min(JoinCondition), string_agg('(' + JoinCondition + ')', ' or ')) as AllConstraintsJoinCondition | |
from sys.foreign_keys | |
join ( | |
select | |
constraint_object_id, | |
string_agg(referenced_columns.name + ' = ' + parent_tables.name + '.' + parent_columns.name, ' and ') as JoinCondition | |
from sys.foreign_key_columns | |
join sys.columns as referenced_columns on referenced_columns.object_id = foreign_key_columns.referenced_object_id and referenced_columns.column_id = foreign_key_columns.referenced_column_id | |
join sys.tables as parent_tables on parent_tables.object_id = foreign_key_columns.parent_object_id | |
join sys.columns as parent_columns on parent_columns.object_id = foreign_key_columns.parent_object_id and parent_columns.column_id = foreign_key_columns.parent_column_id | |
group by constraint_object_id | |
) as JoinConditionsByConstraint on JoinConditionsByConstraint.constraint_object_id = foreign_keys.object_id | |
where foreign_keys.delete_referential_action_desc = 'NO_ACTION' | |
group by referenced_object_id, parent_object_id | |
) as JoinConditionsByTablePair | |
join sys.tables as referenced_tables on referenced_tables.object_id = JoinConditionsByTablePair.referenced_object_id | |
join sys.schemas as referenced_schemas on referenced_schemas.schema_id = referenced_tables.schema_id | |
join sys.tables as parent_tables on parent_tables.object_id = JoinConditionsByTablePair.parent_object_id | |
join sys.schemas as parent_schemas on parent_schemas.schema_id = parent_tables.schema_id | |
where referenced_schemas.name = @schemaName and referenced_tables.name = @tableName | |
) as PartBuilding; | |
execute (@builder); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment