SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE SCHEMA tlg;





CREATE TABLE tlg.AggregateValue_0_100000 (TagUID NVARCHAR(10));
CREATE TABLE tlg.AggregateValue_0_100001 (TagUID NVARCHAR(10));
CREATE TABLE TLG.TagValueCompressed_0_100000 (TagUID NVARCHAR(10));
CREATE TABLE TLG.TagValueCompressed_0_100001 (TagUID NVARCHAR(10));
INSERT INTO tlg.AggregateValue_0_100000 VALUES ('Tag01'), ('Tag02');
INSERT INTO tlg.AggregateValue_0_100001 VALUES ('Tag11');
INSERT INTO TLG.TagValueCompressed_0_100000 VALUES ('Tag01'), ('Tag03');
INSERT INTO TLG.TagValueCompressed_0_100001 VALUES ('Tag11'), ('Tag12'), ('Tag13');
8 rows affected

SELECT '0_100000' AS TableSet
     , ISNULL(AggValue.TagUID, TagValue.TagUID) AS TagUID
     , IIF(TagValue.TagUID IS NOT NULL, 1, 0) AS ExistsInTag
     , IIF(AggValue.TagUID IS NOT NULL, 1, 0) AS ExistsInAgg
  FROM (SELECT DISTINCT TagUID FROM tlg.AggregateValue_0_100000) AggValue
  FULL OUTER
  JOIN (SELECT DISTINCT TagUID FROM TLG.TagValueCompressed_0_100000) TagValue
    ON AggValue.TagUID = TagValue.TagUID


TableSet
TagUID
ExistsInTag
ExistsInAgg


0_100000
Tag01
1
1

0_100000
Tag02
0
1

0_100000
Tag03
1
0


DECLARE @Query NVARCHAR(MAX) = ''

DECLARE @QueryTemplate NVARCHAR(MAX) = '
SELECT ''$SUFFIX$'' AS TableSet
     , ISNULL(AggValue.TagUID, TagValue.TagUID) AS TagUID
     , IIF(TagValue.TagUID IS NOT NULL, 1, 0) AS ExistsInTag
     , IIF(AggValue.TagUID IS NOT NULL, 1, 0) AS ExistsInAgg
  FROM (SELECT DISTINCT TagUID FROM tlg.AggregateValue_$SUFFIX$) AggValue
  FULL OUTER
  JOIN (SELECT DISTINCT TagUID FROM TLG.TagValueCompressed_$SUFFIX$) TagValue
    ON AggValue.TagUID = TagValue.TagUID';

SELECT @query += CONCAT(REPLACE(@QueryTemplate, '$SUFFIX$', RIGHT(name, 8)), ' UNION ALL ')
  FROM sys.tables
 WHERE name LIKE 'TagValueCompressed%';


SET @Query = LEFT(@Query, LEN(@Query) - LEN('UNION ALL '));

EXECUTE sp_executesql @Query







TableSet
TagUID
ExistsInTag
ExistsInAgg


0_100000
Tag01
1
1

0_100000
Tag03
1
0

0_100000
Tag02
0
1

0_100001
Tag11
1
1

0_100001
Tag12
1
0

0_100001
Tag13
1
0

TableSet	TagUID	ExistsInTag	ExistsInAgg
0_100000	Tag01	1	1
0_100000	Tag03	1	0
0_100000	Tag02	0	1
0_100001	Tag11	1	1
0_100001	Tag12	1	0
0_100001	Tag13	1	0