1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46
| SELECT ROW_NUMBER() OVER(ORDER BY name) AS IDN, name
INTO #T_DATABASES
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution', 'semanticdb')
AND name NOT LIKE 'ReportServer%';
CREATE TABLE #T_TABLES (TABLE_SCHEMA sysname, TABLE_NAME sysname);
DECLARE @SQL NVARCHAR(max)
= N'SELECT TABLE_SCHEMA, TABLE_NAME FROM ['
+ (SELECT name FROM #T_DATABASES WHERE IDN = 1) +
+ '].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''';
INSERT INTO #T_TABLES
EXEC (@SQL);
GO
CREATE DATABASE _DB_FEDERATION;
GO
USE _DB_FEDERATION
GO
DECLARE @TABLE_SCHEMA sysname, @TABLE_NAME sysname, @SQL NVARCHAR(max);
DECLARE C CURSOR
FOR SELECT TABLE_SCHEMA, TABLE_NAME
FROM #T_TABLES;
OPEN C;
FETCH C INTO @TABLE_SCHEMA, @TABLE_NAME;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'';
SELECT @SQL = @SQL + N'SELECT * FROM [' + name + N'].[' + @TABLE_SCHEMA + N'].[' + @TABLE_NAME + N'] UNION ALL '
FROM #T_DATABASES;
SET @SQL = N'CREATE VIEW [' + @TABLE_SCHEMA + '].[V_' + @TABLE_NAME + '] AS '
+ LEFT(@SQL , LEN(@SQL) - 10);
PRINT @SQL
EXEC (@SQL);
FETCH C INTO @TABLE_SCHEMA, @TABLE_NAME;
END;
CLOSE C;
DEALLOCATE C;
DROP TABLE #T_DATABASES;
DROP TABLE #T_TABLES; |
Partager