SQL Stuff

Get All Database Info Including Age Size Data Log etc.

Here’ s some SQL logic to give you all database info such as Server Name, Database Name, Size, Age, Recovery Model, including data and log file information. This isn’t that difficult to write, but it’s always nice to have these in your go-to scripts whenever you need information quickly.


use master;

set nocount on

if object_id('tempdb..#database_info') is not null

drop table #database_info

create table #database_info

(

[database_name] varchar(255)

, [database_size] float

, [remarks] varchar(255)

)

insert #database_info exec ('exec master..sp_databases');

select

'server_name' = upper(@@servername)

, 'database' = upper(database_name)

, 'created_on' = left(sd.create_date, 19)

, 'database_age' =

cast(datediff(second, sd.create_date, getdate()) / 60 / 60 / 24 / 30 / 12 as nvarchar(50)) + ' years, '

+ cast(datediff(second, sd.create_date, getdate()) / 60 / 60 / 24 / 30 % 12 as nvarchar(50)) + ' months, '

+ cast(datediff(second, sd.create_date, getdate()) / 60 / 60 / 24 % 30 as nvarchar(50)) + ' days, '

+ cast(datediff(second, sd.create_date, getdate()) / 60 / 60 % 24 as nvarchar(50)) + ' hours, '

+ cast(datediff(second, sd.create_date, getdate()) / 60 % 60 as nvarchar(50)) + ' minutes '

+ cast(datediff(second, sd.create_date, getdate()) % 60 as nvarchar(50)) + ' seconds '

, 'status' = sd.state_desc

, 'in_gb' = round((database_size / 1024) / 1024, 2)

, 'in_mb' = round(database_size / 1024, 2)

, 'in_kb' = database_size

, 'recovery_model' = sd.recovery_model_desc

, 'data_logical_name' = smf_data.name

, 'data_path' = smf_data.physical_name

, 'log_logical_name' = smf_log.name

, 'log_path' = smf_log.physical_name

, 'compatability_level' = case sd.compatibility_level

when 60 then '60 = SQL 6.0 (Works on 6)'

when 65 then '65 = SQL 6.5 (Works on 6.5)'

when 70 then '70 = SQL 7.0 (Works on 7)'

when 80 then '80 = SQL 2000 (Works on 2000)'

when 90 then '90 = SQL 2005 (Works on 2005, 2000)'

when 100 then '100 = SQL 2008 (Works on 2008R2, 2005)'

when 110 then '110 = SQL 2012 (Works on 2012, 2008R2, 2005)'

when 120 then '120 = SQL 2014 (Works on 2014, 2012, 2008R2)'

when 130 then '130 = SQL 2016 (Works on 2016, 2014, 2012, 2008R2)'

when 140 then '140 = SQL 2017 (Works on 2017, 2016, 2014, 2012, 2008R2)'

end

from

#database_info dbi

inner join sys.databases sd on dbi.database_name = sd.name

inner join (select smf.database_id, smf.name, smf.physical_name from sys.master_files smf where smf.file_id = 1) smf_data on smf_data.database_id = sd.database_id

inner join (select smf.database_id, smf.name, smf.physical_name from sys.master_files smf where smf.file_id = 2) smf_log on smf_log.database_id = sd.database_id

where

sd.database_id > 4

order by

database_size desc;

Advertisements

Categories: SQL Stuff

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