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|
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
'
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)
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With