Get All SQL SERVER Database Information with multiple file groups using sys.databases and sys.master_files

Here is a quick script to find ALL databases on a server with size and file locations and a little more.  If multiple file groups exist it will put them in a pipe delimited format in the datafile or log file locations  
Example: Query Information From All Databases On A SQL Server Instance
-- Get database information, file locations and sizes
-- xp_fixeddrives may need to be commented out in some environments
-- comment out lines as needed 
IF OBJECT_ID('tempdb..#DriveInfo') IS NOT NULL
 DROP TABLE #DriveInfo
CREATE TABLE #DriveInfo(Drive CHAR(1),MBFree INT)

INSERT INTO #DriveInfo
      EXEC master..xp_fixeddrives
-------------------------------------------------------------
-- Drop #Tmp_tblDatabaseInfo table if it exists
IF OBJECT_ID('tempdb..#Tmp_tblDatabaseInfo') IS NOT NULL
 DROP TABLE #Tmp_tblDatabaseInfo

-- Create #Tmp_tblDatabaseInfo table for population
CREATE TABLE #Tmp_tblDatabaseInfo([ServerName] [nvarchar](128) NULL
      ,[DBName] [nvarchar](128)  NULL
      ,[database_id] [int] NULL
      ,[create_date] datetime NULL
      ,[CompatibilityLevel] [int] NULL
      ,[collation_name] [nvarchar](128) NULL
      ,[state_desc] [nvarchar](60) NULL
      ,[recovery_model_desc] [nvarchar](60) NULL
      ,[Last backup] [nvarchar](4000)
      ,[DataFiles] [INT] NULL
      ,[DataFileLocations] [nvarchar](4000)
      ,[DataFilesMB] money null
      ,DataVolumeFreeSpaceMB INT NULL
      ,[LogFiles] [INT] NULL
      ,[LogFileLocations] [nvarchar](4000)
      ,[LogFilesMB] money null
      ,LogVolumeFreeSpaceMB INT NULL) ON [PRIMARY]


-- Populate data info from sys.master_files and sys.databases and msdb..backupset
INSERT INTO #Tmp_tblDatabaseInfo
SELECT @@SERVERNAME AS [ServerName] ,d.name AS DBName  ,d.database_id ,d.create_date ,d.compatibility_level 
      ,CAST(d.collation_name AS [nvarchar](128)) AS collation_name
      ,d.[state_desc] ,d.recovery_model_desc,
            ISNULL((SELECT TOP 1
CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +
LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +
' (' + CAST(DATEDIFF(second, BK.backup_start_date,
BK.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'seconds)'
FROM msdb..backupset BK WHERE BK.database_name = d.name ORDER BY backup_set_id DESC),'-') AS [Last backup]
      ,(select count(1) from sys.master_files m WHERE m.type = 0 and m.database_id = d.database_id)  AS DataFiles
      ,(select physical_name + ' | ' AS [text()]
         from sys.master_files m
         WHERE m.type = 0 and m.database_id = d.database_id
         ORDER BY file_id
         FOR XML PATH ('')) AS DataFileLocations
      ,(select sum(size) from sys.master_files m WHERE m.type = 0 and m.database_id = d.database_id)  AS DataFilesMB
      ,NULL
      ,(select count(1) from sys.master_files m WHERE m.type = 1 and m.database_id = d.database_id)  AS LogFiles
      ,(select physical_name + ' | ' AS [text()]
         from sys.master_files m
         WHERE m.type = 1 and m.database_id = d.database_id
         ORDER BY file_id
         FOR XML PATH ('')) AS LogFileLocations
      ,(select sum(size) from sys.master_files m WHERE m.type = 1 and m.database_id = d.database_id)  AS LogFilesMB
      ,NULL
FROM  sys.databases d 
WHERE d.database_id > 4 --Exclude system databases as needed

UPDATE #Tmp_tblDatabaseInfo
   SET DataFileLocations =
      CASE WHEN LEN(DataFileLocations) > 4 THEN  LEFT(DataFileLocations,LEN(DataFileLocations)-2) ELSE NULL END
   ,LogFileLocations =  CASE WHEN LEN(LogFileLocations) > 4 THEN  LEFT(LogFileLocations,LEN(LogFileLocations)-2) ELSE NULL END
   ,DataFilesMB =  CASE WHEN DataFilesMB > 0 THEN  DataFilesMB / 128.0   ELSE NULL END
   ,LogFilesMB =  CASE WHEN LogFilesMB > 0 THEN  LogFilesMB / 128.0  ELSE NULL END
-- *** EXEC master..xp_fixeddrives not used for DriveInfo comment out the below two lines 
   ,DataVolumeFreeSpaceMB = (SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( DataFileLocations,1))
   ,LogVolumeFreeSpaceMB =  (SELECT MBFree FROM #DriveInfo WHERE Drive = LEFT( LogFileLocations,1))
-- *** EXEC master..xp_fixeddrives not used for DriveInfo comment out the below two lines 

-- Review the populated table
select * from #Tmp_tblDatabaseInfo
You can alternately omit the drive free space or include the system databases as needed.





Comments

Post a Comment

Popular posts from this blog

How to mass update all or some sql server agent job step commands

How to mass update all or some sql server agent job step and commands using TSQL.   This will use the built-in Replace function to search text in SQL agent jobs  SQL Agent Job information is stored in MSDB and can reviewed using some of the following queries: SELECT * FROM msdb.dbo.SysJobs SELECT * FROM msdb.dbo.SysJobSteps -- OR SELECT a.name as job_name, b.step_name, b.subsystem, b.command, b.database_name, a.enabled       ,a.description, a.date_created, b.job_id, b.step_id   FROM [dbo].[sysjobsteps] as b   INNER JOIN sysjobs as a ON a.job_id = b.job_id Since I will be focusing on the Commands column I will look at using this query to extract data into a temp table so that I can test any updates with the replace step before doing an update to MSDB. This would be your opportunity to archive or save any commands as needed.  Some people may prefer to generate all the syntax necessary to use EXEC sp_update_jobstep so you a...
Read more

SQL Monitoring for Blocking and Locking Capture and Log to table with sp_WhoIsActive or sp_BlitzWho

Image
You can use  sp_WhoIsActive or sp_BlitzWho and log the capture data to a table.   In my example I will use sp_whoisactive as I am more familiar with its use and data collection, however I will look at creating a future post using BlitzWho.  I will provide a few examples that were used to determine that the paid SQL monitoring products can be fancy tools but can be useless if you don't understand the basic concepts or even an understanding of how we capture similar data or in some cases more finely tuned to a specific scenario or use case.  Sometimes these paid solutions are incorrect or things can simply go wrong (Services Fail to start or the data is aggregated and misses something like the query commands and SQL Fragments) and it's always good to plan ahead and be prepared.   Sp_WhoIsActive is a procedure written by Adam Machanic. It can be found here:  sp_whoisactive downloads Sp_BlitzWho is a procedure written by the Brent Ozar team....
Read more

SQL Server General Index Naming Convention

SQL Server General Index Naming Convention  easy to follow:  PK_ for primary keys PK_  Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index.  IX_ for non clustered non unique indexes IX_  standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for  I nde X ). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes. UK_ for unique keys UK_  for unique key index UX_ for unique indexes UX_  for unique index. CLIX_ for Clustered Index (non PK) CLIX_  (non-PK) Clustered Index name is prefixed with CLIX (for  CL ustered  I nde X ) Additionally, I like to code all Create Indexes in SQL 2012 and above with an if not ...
Read more