Uncategorized

Generate UNION ALL views for all tables from all databases with the same schema

26. 5. 2019

This scritp can be used to generate views to UNION ALL rows from all tables within two or more databases with the same schema. Replace $SCHEMA_SOURCE_DB$ with the name of the database you will take the schema from.

  • All databases in the [db] list must have the same schema.
  • Requires SQL 2017 and above because of STRING_AGG() function is used
;WITH [db] AS
(
	SELECT [d].[name] [DbName]
	FROM [sys].[databases] [d]
	WHERE [d].[name] LIKE 'FILTER%'
),
[tbl] AS
(
SELECT [s].[name] [SchemaName], [t].[name] [TableName], [db].[DbName],
'	SELECT ''' + [db].[DbName] + ''' DbName, ' + STRING_AGG('[' + CAST([c].[name] AS NVARCHAR(MAX)) + ']', ', ') WITHIN GROUP (ORDER BY [c].[column_id] ASC) + CHAR(13) + 
'	FROM [' + [db].[DbName] + '].[' + [s].[name] + '].[' + [t].[name] + ']' +
CASE
	WHEN LEAD([t].[name], 1) OVER(ORDER BY [t].[name]) = ISNULL([t].[name], [t].[name]) THEN CHAR(13) + CHAR(13) + '	UNION ALL' + CHAR(13) + CHAR(13)
	ELSE ''
END [Stmt]
FROM [$SCHEMA_SOURCE_DB$].[sys].[tables] [t]
	INNER JOIN [$SCHEMA_SOURCE_DB$].[sys].[schemas] [s] ON [s].[schema_id] = [t].[schema_id]
	INNER JOIN [$SCHEMA_SOURCE_DB$].[sys].[columns] [c] ON [c].[object_id] = [t].[object_id]
	CROSS JOIN [db]
GROUP BY [db].[DbName], [s].[name], [t].[name]
)
SELECT 
	[tbl].[SchemaName], [tbl].[TableName], 
	'CREATE VIEW [dbo].[v_' + [tbl].[SchemaName] + '_' + [tbl].[TableName] + ']' + CHAR(13) + 
	'AS' + CHAR(13) + 
	STRING_AGG(CAST([Stmt] AS NVARCHAR(MAX)), '') WITHIN GROUP (ORDER BY [Stmt] ASC) + CHAR(13) + 
	'GO' + 
	CHAR(13)
FROM [tbl]
GROUP BY [tbl].[SchemaName], [tbl].[TableName]
ORDER BY [tbl].[SchemaName], [tbl].[TableName]

 

87
Share: