Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: sp_MSforeachdb into single result set

Tags:

sql-server

I am going to archive to get a query which pulls some data from all databases and return a single result set.

Right now I have the following:

DECLARE @command varchar(4000)
SELECT @command = '
USE [?] 
SELECT 
    database_name = DB_NAME(database_id)
    , log_size_mb = CAST(SUM(CASE WHEN type_desc = "LOG" THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , row_size_mb = CAST(SUM(CASE WHEN type_desc = "ROWS" THEN size END) * 8. / 1024 AS DECIMAL(8,2))
    , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
    , Kostenstelle = (select value from sys.extended_properties WHERE name =     "Kostenstelle")
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID()
GROUP BY database_id
'
EXEC sp_MSforeachdb @command

The code above will return in multiple result sets.

The result should look like the following:

|database_name|log_size_mb|row_size_mb|Kostenstelle|
+-------------+-----------+-----------+------------+
|demoA        |         12|         10|        xxxx|
|demoB        |         52|         12|        xxxx|
like image 826
Alex Avatar asked May 30 '15 09:05

Alex


2 Answers

Got a reply from a colleague - 1000 thanks

DECLARE @command varchar(4000)
SELECT @command = '
USE [?] 
SELECT 
database_name = CAST(DB_NAME(database_id) AS VARCHAR(50))
, log_size_mb = CAST(SUM(CASE WHEN type_desc = "LOG" THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = "ROWS" THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
, Kostenstelle = CAST((select value from sys.extended_properties WHERE     name =     "Kostenstelle") AS VARCHAR(10))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID()
GROUP BY database_id
'

DECLARE @DatabasesKst TABLE
(
  database_name VARCHAR(50),
  log_size_mb DECIMAL(8,2),
  row_size_mb DECIMAL(8,2),
  total_size DECIMAL(8,2),
  Kostenstelle VARCHAR(100)
)

INSERT  INTO @DatabasesKst
EXEC sp_MSforeachdb @command

select * from @DatabasesKst

..

If you want to filter out some of the databases, you can wrap the 'USE [?]' with an IF/BEGIN/END like this.

DECLARE @command varchar(4000)
SELECT @command = '
IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN 
    USE [?]


    SELECT 
     (your select/from/where statement here)


 END
'
like image 160
Alex Avatar answered Nov 05 '22 06:11

Alex


You can create table in some db and save results in this table if you add INSERT statement in your query :

DECLARE @command varchar(4000)
SELECT @command = '
USE [?] 
INSERT INTO <DbName>.<SchemaName>.<TableName>
SELECT 
database_name = DB_NAME(database_id)
...
like image 20
Max Avatar answered Nov 05 '22 04:11

Max