Find the complete details of all the databases hosted in SQL Server Instance
Some times to find the database details we need to run multiple queries.
Below one query will retrieve the following information which helps in quick understanding of the databases hosted in a particular SQL Server Instance.
1. DB Name
2. Logical File Name
3. File Size
4. Physical name and Path
5. Size
6. Percentage of Growth
7. Occupied Space
8. Free Space
9. Status
10. Recovery model
DECLARE @DBInfo TABLE ( ServerName VARCHAR(100), DatabaseName VARCHAR(100), FileSizeMB INT, LogicalFileName SYSNAME, PhysicalFileName NVARCHAR(520), Status SYSNAME, Updateability SYSNAME, RecoveryMode SYSNAME, FreeSpaceMB INT, FreeSpacePct VARCHAR(7), FreeSpacePages INT,Autogrow VARCHAR(100), PollDate DATETIME) DECLARE @command VARCHAR(5000)SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sys.database_files.size/128.0 AS int) AS FileSize,
sys.database_files.name AS LogicalFileName, sys.database_files.physical_name AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sys.database_files.size/128.0 - CAST(FILEPROPERTY(sys.database_files.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sys.database_files.size/128.0 -CAST(FILEPROPERTY(sys.database_files.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sys.database_files.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
CASE WHEN is_percent_growth = 0 THEN LTRIM(STR(growth * 8.0 / 1024,10,1)) + '' MB,''
ELSE ''By '' + CAST(growth AS VARCHAR) + '' percent, '' END +
CASE WHEN max_size = -1 THEN ''unrestricted growth''
ELSE ''restricted growth to '' + LTRIM(STR(max_size * 8.0 / 1024,10,1)) + '' MB''
END AS Autogrow,
GETDATE() as PollDate FROM sys.database_files' INSERT INTO @DBInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
Autogrow,
PollDate) EXEC sp_MSforeachdb @command SELECT
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
Autogrow,
PollDate FROM @DBInfo WHERE DatabaseName NOT IN ('Master','Model','tempdb') ORDER BY
ServerName,
DatabaseName
