试试这个-
SELECt d.server_name , d.sversion_name , d.edition , max_db_size_in_gb = CASE WHEN engine_edition = 4 THEN CASE WHEN d.sversion_name LIKE '%2012%' THEN 10 WHEN d.sversion_name LIKE '%2008 R2%' THEN 10 WHEN d.sversion_name LIKE '%2008%' THEN 4 WHEN d.sversion_name LIKE '%2005%' THEN 4 END ELSE -1 ENDFROM ( SELECtsversion_name = SUBSTRINg(v.ver, 0, CHARINDEX('-', v.ver) - 1) , engine_edition = SERVERPROPERTY('EngineEdition') , edition = SERVERPROPERTY('Edition') , server_name = SERVERPROPERTY('ServerName') FROM (SELECt ver = @@VERSION) v) d另外，请尝试使用此查询来获取有关DB的扩展信息-
IF EXISTS( SELECT 1 FROM tempdb.dbo.sysobjects WHERe id = OBJECT_ID('tempdb.dbo.#DBObj')) DROp TABLE #DBObjCREATE TABLE #DBObj( [DB] SYSNAME , reservedpages INT , usedpages INT , pages INT)EXEC sys.sp_MSforeachdb ' USE [?] INSERT INTO #DBObj ( [DB] , reservedpages , usedpages , pages ) SELECTDB_NAME() , pg.reservedpages , pg.usedpages , pg.pages FROM ( SELECt reservedpages = SUM(a.total_pages) , usedpages = SUM(a.used_pages) , pages = SUM( CASE WHEN it.internal_type IN (202, 204, 207, 211, 212, 213, 214, 215, 216, 221, 222) THEN 0 WHEN a.[type] != 1 AND p.index_id < 2 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END ) FROM sys.partitions p JOIN sys.allocation_units a ON p.[partition_id] = a.container_id LEFT JOIN sys.internal_tables it ON p.[object_id] = it.[object_id] ) pg'SELECt d.name , total_size_mb = data.row_size_mb + data.log_size_mb , data.log_size_mb , data.row_size_mb , reserved_space_mb = CAST(do.reservedpages * 8. / 1024 AS DECIMAL(10,2)) , data_size_mb = CAST(do.pages * 8. / 1024 AS DECIMAL(10,2)) , index_size_mb = CAST((do.usedpages - do.pages) * 8. / 1024 AS DECIMAL(10,2)) , unused_size_mb = CAST((do.reservedpages - do.usedpages) * 8. / 1024 AS DECIMAL(10,2)) , unallocated_space_mb = CAST(CASE WHEN data.row_size >= do.reservedpages THEN (data.row_size - do.reservedpages) * 8. / 1024 ELSE 0 END AS DECIMAL(10,2))FROM ( SELECt df2.database_id , log_size_mb = CAST(df2.log_size * 8. / 1024 AS DECIMAL(10,2)) , row_size_mb = CAST(df2.row_size * 8. / 1024 AS DECIMAL(10,2)) , df2.log_size , df2.row_size FROM ( SELECt df.database_id , log_size = SUM(CASE WHEN df.type_desc = 'LOG' THEN df.size END) , row_size = SUM(CASE WHEN df.type_desc = 'ROWS' THEN df.size END) FROM sys.master_files df GROUP BY df.database_id ) df2) dataJOIN sys.databases d ON data.database_id = d.database_idJOIN #DBObj do ON do.DB = d.nameORDER BY data.row_size_mb + data.log_size_mb DESC
