SQL Server Discovery

Better, faster, cheaper ...pick two.
posts - 13, comments - 80, trackbacks - 0

Gather a Database Inventory

As a DBA for a large corporation, I manage literally hundreds of databases. In order to help keep track of all the databases and log files, I needed a way to quickly gather an inventory of all databases located on each sql named instance, so I came up with this simple little script that also includes a filesize in megabytes column: 


USE [Master]
GO 
SELECT 
	sysdatabases.name AS 'db_name'  
,	(select 'file_type' =  
	CASE  
		WHEN sysaltfiles.groupid = 1 THEN 'data'
		WHEN sysaltfiles.groupid = 0 THEN 'log'
	END) as 'file_type'	
,	sysaltfiles.name AS 'file_name'
,	(sysaltfiles.size * 8 / 1024) AS 'file_size(MB)' -- file size in MB
FROM 
	dbo.sysdatabases
JOIN
	dbo.sysaltfiles ON
	(dbo.sysdatabases.dbid=dbo.sysaltfiles.dbid)
WHERE
	sysdatabases.dbid NOT IN ('1','2','3','4')
ORDER BY
	dbo.sysdatabases.name


Run this script in the Master database and it will output a database list that you can export to a spreadsheet for reporting or futher analysis. If anyone knows a better way to do this or some enhancement suggestions to include additional useful information, let me know.

Print | posted on Wednesday, February 28, 2007 9:40 AM |

Feedback

Gravatar

# re: Gather a Database Inventory

I would use the new catalog views - sys.master_files and sys.databases. Also, it would be nice if I could also see the status of the database as well (ONLINE, EMERGENCY, etc.).
3/1/2007 8:53 PM | Raju Joseph
Gravatar

# re: Gather a Database Inventory

Thanks for the tip Raju. I will play around with the script and see if I can add those columns.

Stay tuned
3/2/2007 9:03 AM | Phil
Gravatar

# re: Gather a Database Inventory

calling it file_name is a little bit tricky. The sysaltfiles.filename is the physical file name which you may also want to output.
5/13/2007 4:23 PM | TOm
Gravatar

# re: Gather a Database Inventory

Thanks for the script. Work great.
7/23/2007 9:53 AM | Joe
Gravatar

# re: Gather a Database Inventory

Add To total size of the Database
10/29/2007 4:17 AM | Soumadip Banerjee
Gravatar

# re: Gather a Database Inventory

This works great but if you are admin of several SQL servers can you write this to get the data from all servers on a domain?
5/29/2008 3:48 PM | Patrick
Gravatar

# re: Gather a Database Inventory

salam
6/29/2009 6:13 AM | mukhtar aqeel
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET