问题描述
我有多个数据库具有相同的表(具有不同值的Eventlog)。这些数据库的名称可能会更改。我正在尝试使用相应的数据库名称在一个统一表中显示事件日志表。
我尝试使用游标和动态SQL语句来实现这一点,但没有成功。此外,我不确定这是不是最好的方法。我需要一些帮助!
-- Create a new table variable to record all the database name DECLARE @Database_Table table ([TimeStamp] nvarchar(500) ,[EventIDNo] nvarchar(100) ,[EventDesc] nvarchar(1000)) --Create variable for database name and query variable DECLARE @DB_Name VARCHAR(100) -- database name DECLARE @query NVARCHAR(1000) -- query variable --Declare the cursor DECLARE db_cursor CURSOR FOR -- Populate the cursor with the selected database name SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb') --Open the cursor OPEN db_cursor --Moves the cursor to the first point and put that in variable name FETCH NEXT FROM db_cursor INTO @DB_Name -- while loop to go through all the DB selected WHILE @@FETCH_STATUS = 0 BEGIN SET @query = N'INSERT INTO @Database_Table SELECT @DB_Name, * FROM ['+ @DB_Name +'].dbo.EventLog_vw as A' EXEC (@query) --Fetch the next record from the cursor FETCH NEXT FROM db_cursor INTO @DB_Name END --Close and deallocate cursor CLOSE db_cursor DEALLOCATE db_cursor SELECT * FROM @Database_Table
推荐答案
如果您需要单个结果集,并且所有表格都具有相同的布局,则应该可以这样做:
DECLARE @sql nvarchar(4000) = (SELECT STRING_AGG(CONCAT( 'SELECT ''', QUOTENAME(name), ''', * FROM ', QUOTENAME(name), '..Table ', CHAR(10) ), ' UNION ALL ' + CHAR(10)) FROM sys.databases); SELECT @sql; -- for checking EXEC(@sql);如果您的兼容级别为130或更低,则必须使用XML PATH(TYPE, '')进行聚合。我会把这个留给你。;