SQL – Query to find data,log,Total Size and other Useful information of all databases – SQL 2000/2005/2008/R2

I’ve encountered a situation where I need to find out the database file,log file, total size and other useful information about a database on SQL Server 2000/2005/2008/R2 versions. The requirement is to use same query and it has to be executed across all version of SQL SERVER.  We can  also do this using dynamic SQL’s.

Here is the T-SQL which you can use to run on any version of SQL Server to fetch the result.

USE MASTER
Go

SELECT @@SERVERNAME Servername,
CONVERT(VARCHAR(25), DB.name) AS dbName,
CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, ‘status’)) AS [Status],
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB],
(SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB],
(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB,
convert(sysname,DatabasePropertyEx(name,’Updateability’)) Updateability,
convert(sysname,DatabasePropertyEx(name,’UserAccess’)) UserAccess ,
convert(sysname,DatabasePropertyEx(name,’Recovery’)) RecoveryModel ,
convert(sysname,DatabasePropertyEx(name,’Version’)) Version ,
CASE cmptlevel
WHEN 60 THEN ’60 (SQL Server 6.0)’
WHEN 65 THEN ’65 (SQL Server 6.5)’
WHEN 70 THEN ’70 (SQL Server 7.0)’
WHEN 80 THEN ’80 (SQL Server 2000)’
WHEN 90 THEN ’90 (SQL Server 2005)’
WHEN 100 THEN ‘100 (SQL Server 2008)’
END AS [compatibility level],
CONVERT(VARCHAR(20), crdate, 103) + ‘ ‘ + CONVERT(VARCHAR(20), crdate, 108) AS [Creation date],
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.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),’-‘) AS [Last backup]
FROM sysdatabases DB
ORDER BY dbName, [Last backup] DESC, NAME

Download the code here DB Useful Information

Output :-

Image

About these ads

About Prashanth Jayaram

I’m a Database technologist having 8+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell. hobbies are playing sports and drawing.
This entry was posted in SQL, T-SQL and tagged , , , . Bookmark the permalink.

6 Responses to SQL – Query to find data,log,Total Size and other Useful information of all databases – SQL 2000/2005/2008/R2

  1. Pingback: Query to get database information | Random Technical Thoughts

  2. chrisbarba says:

    This is some great information. There were some errors in your script (you can’t just copy and paste it in SSMS to get it to run). I posted about your script on my blog (giving you credit and linking back your blog) and I corrected the script.
    Here is a link to my posting:
    http://wp.me/psiID-80

    • Thank you for correcting. Somehow the copy and paste did not work hence I used to attach the script as an attachment

      • chrisbarba says:

        No problem, they were little changes like sysdatabases should be sys.databases.

        This is a great script thanks for coming up with it. I’m glad to put it in my toolbox.

      • This T-SQL need to be run under master database. The intention is to run the queries on all version of SQL Server from 2000 onward. I just updated the SQL to use Master database. You have a great collection of good articles, Keep it up. Thanks again.

  3. Pingback: bhaveshkhanpara | Most useful Sql Queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s