Skip to content

Instantly share code, notes, and snippets.

@jnm2
Created January 29, 2024 20:08
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jnm2/72f8776a5335675229044ea317b58312 to your computer and use it in GitHub Desktop.
Save jnm2/72f8776a5335675229044ea317b58312 to your computer and use it in GitHub Desktop.
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