用鵝毛筆沾咖啡畫ER Model

declare @dbName nvarchar(50),
             @query nvarchar(150);

set @dbName = 'EquAnal_9cb8897850180582';
set @query = 'select * from ['+@dbName+'].[dbo].[consecMins]';

EXEC (@query)

declare @equAnalDbName table
(
    ID INT ,  --ROW序號
    name NVARCHAR(100)
)

insert into @equAnalDbName (ID, name)
(SELECT 
    ROW_NUMBER() OVER(ORDER BY name) AS ID, name 
    FROM master.sys.databases
    where name like 'EquAnal_%')

select * from @equAnalDbName

declare @equAnalDbName table

(

    ID INT ,  --ROW序號

        name NVARCHAR(100)

)

insert into @equAnalDbName (ID, name)

(SELECT

ROW_NUMBER() OVER(ORDER BY name) AS ID,

name

FROM master.sys.databases

where name like 'EquAnal_%')

DECLARE @TabelCount INT --loop的條件

   ,@WhileTableCount INT = 1

   --

   ,@dbNum INT

   ,@dbName VARCHAR(100)

   ,@query nvarchar(500)

   ,@unionQuery nvarchar(4000)

   SET @unionQuery = '';

   SET @TabelCount = (SELECT COUNT(ID) FROM @equAnalDbName);

--以下開始loop

WHILE @WhileTableCount <= @TabelCount

BEGIN

--透過@WhileTableCount的定位取得每個row的值

SELECT @dbNum = ID, @dbName = name from @equAnalDbName

where ID = @WhileTableCount

    ----Do Something Start----

if(@WhileTableCount > 1)

begin

set @unionQuery += ' union ';

end

set @query = ' (select *, '''+@dbName+''' as dbName from ['+@dbName+'].[dbo].[consecMins] ) ';

set @unionQuery += @query;

----Do Something End----

--迴圈遞增

SET @WhileTableCount = @WhileTableCount + 1

END

set @unionQuery = @unionQuery + ' order by endDateMin desc';

EXEC (@unionQuery);