Please try this way:
DECLARE @c_db_names AS TABLE
(
ID INT IDENTITY(1,1),
name sysname
)
DECLARE @Records AS TABLE
(
ID INT IDENTITY(1,1),
name sysname,
OrderID NVARCHAR(10),
OrderStatus NVARCHAR(10),
OrderDetails NVARCHAR(10),
FulfillmentDate DATETIME,
ShippingDate DATETIME,
AddDate DATETIME
)
DECLARE @SQL NVarchar(3000)
DECLARE @ID INT
DECLARE @DBName sysname
SET NOCOUNT ON
INSERT @c_db_names
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN('master','AdventureWorks2008','TestDB','Development','Northwind')
SELECT @ID = COUNT(1) FROM @c_db_names
WHILE (@ID > 0)
BEGIN
SELECT @DBName = name
FROM @c_db_names
WHERE ID = @ID
SET @ID = @ID - 1
SET @SQL = 'SELECT ''' + @DBName + ''' AS [Name], AO.OrderID, AO.OrderStatus, AO.OrderDetails, FS.FulfillmentDate, FS.ShippingDate, FS.AddDate' + CHAR(13) + CHAR(10)
+ 'FROM ' + @DBName +'.dbo.ActiveOrders AS AO ' + CHAR(13) + CHAR(10)
+ 'INNER JOIN '+ @DBName +'.dbo.FinalShipmentNumbers AS FS ' + CHAR(13) + CHAR(10)
+ 'ON' + CHAR(13) + CHAR(10)
+ ' AO.OrderDetails = FS.OrderID where addDate between ''1/30/2016'' and ''2/26/2016''' + CHAR(13) + CHAR(10)+ CHAR(13) + CHAR(10)
PRINT @SQL;
INSERT INTO @Records
EXEC SP_EXECUTESQL @SQL
END
SET NOCOUNT OFF
SELECT * FROM @Records