fUH7Ds6g

1IF EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#output_dbinfo'))
2	DROP TABLE #output_dbinfo;
3
4IF NOT EXISTS (SELECT [object_id] FROM tempdb.sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID('tempdb.dbo.#output_dbinfo'))
5	CREATE TABLE #output_dbinfo 
6	(
7		database_name VARCHAR(255), 		
8		current_storage_size_mb int,
9		max_storage_size_mb int
10	)
11
12
13DECLARE @sql NVARCHAR(2000)	
14DECLARE @dbname VARCHAR(255)
15DECLARE curDBs CURSOR FAST_FORWARD FOR 
16	SELECT [name] FROM master.sys.databases (NOLOCK) WHERE is_read_only = 0
17OPEN curDBs
18FETCH NEXT FROM curDBs INTO @dbname
19WHILE (@@FETCH_STATUS = 0)
20BEGIN
21	SET @sql = N'
22	USE [' + @dbname + '];
23	SELECT db_name(db_id()), 
24	current_storage_size_mb, max_storage_size_mb  
25	FROM sys.database_query_store_options
26	where actual_state != 0;'
27
28	INSERT INTO #output_dbinfo 
29	EXEC (@sql)
30
31	FETCH NEXT FROM curDBs INTO @dbname
32END
33CLOSE curDBs
34DEALLOCATE curDBs;
35
36SELECT * FROM #output_dbinfo