query multiple databases for statistic purposes SQL
I am trying to write a stored开发者_StackOverflow中文版 procedure that will allow me to write a single SELECT statement that runs on all databases on my SQL Server. (Microsoft SQL 2005)
so far ive come up with the following procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [sp_cross_db_query]
@SQLQuery varchar(400)
AS
DECLARE @DB_Name varchar(100)
DECLARE database_cursor CURSOR FOR
SELECT DatabaseName
FROM Management.dbo.Customers
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
exec(
'USE [' + @DB_Name + '];' +
@SQLQuery
)
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
to run the query
exec sp_cross_db_query 'SELECT count(*) FROM Products'
by this i can count all products over all the databases, the problem is the way i get my data returned. now i get several 'windows' that holds the result witch is quite unmanageable. so my question is, how can i get a result very much like a SELECT .., UNION SELECT .., UNION SELECT ..? (in other words one result table)?
i had thoughts about creating a temporary table inside the procedure, what do you think?
Thanks in advance!
I think there no other way except temporary table to achieve the functionality you want. You require to add row in temporary table than have to use select @tablevariable to get all data.
Check this : http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-2
Possibly dynamic SQL
DECLARE @DynSQL nvarchar(max)
select
@DynSQL = isnull(@DynSQL + ' UNION ALL ','') +
'SELECT count(*) AS ProdCnt FROM ' + quotename(name) + '..Products'
from sys.databases
WHERE state= 0 AND is_in_standby=0 AND database_id>4 /*Exclude Offline and System
databases*/
PRINT @DynSQL /*For Debugging or use the next line if the print output is truncated*/
SELECT @DynSQL AS [processing-instruction(x)] FOR XML PATH('')
EXEC sp_executesql @DynSQL
精彩评论