Database Inventory

Hello Guys,

This script is dedicated to those of my colleagues who inspire me to work on it as I am not able to see the pain in which they are going thru while collecting server information that they are supporting.

Note: I am Still in process of making changes in this and will keep on updating this
post in future.


Special Thanks : A Special Thanks to ABHY CHAUDHARY my dear buddy who helped me a lot
while work on this.

Alter procedure SQLInventory as
declare @image_path varchar(100)
declare @startup_type int
declare @startuptype varchar(100)
declare @start_username varchar(100)
declare @instance_name varchar(100)
declare @system_instance_name varchar(100)
declare @log_directory varchar(100)
declare @key varchar(1000)
declare @registry_key varchar(100)

SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
if @instance_name!='MSSQLSERVER'
set @instance_name='MSSQL$'+@instance_name

EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;

set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;

SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
if @registry_key is NULL
set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)

SELECT @image_path as 'Binaries Path'
,@start_username as 'Statrtup User'
,@log_directory as 'Error Logs Location'
,@Startuptype
,serverproperty ('ComputerNamePhysicalNetBIOS') as machine
,serverproperty ('Instancename')
,(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'file_type'
, d.database_id as 'DB_ID'
, d.name AS 'db_name'
, s.name AS 'logical_file_name'
, s.filename AS 'physical_file_name'
, (s.size * 8 / 1024) AS 'file_size(MB)' -- file size in MB
,d.compatibility_level
, DATABASEPROPERTYEX (d.name,'Recovery') as Recovert_Model
, DATABASEPROPERTYEX (d.name,'Status') as Database_Status
,serverproperty('productlevel') as ServicePack
,serverproperty('edition') as edition
,serverproperty('productversion') as version
,serverproperty('collation') as collation
,serverproperty('Isclustered') as IS_Clustered
,serverproperty('IsFullTextInstalled') as IS_FullText
,d.is_published as Published
,d.is_subscribed as Subscribed
,d.is_distributor as Distributor
,m.mirroring_state
,substring(k.physical_device_name ,1,59) as backup_directory,
CASE l.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType

FROM
sys.databases d INNER JOIN sys.sysaltfiles s
ON
d.database_id=s.dbid
INNER JOIN sys.database_mirroring m
ON
d.database_id=m.database_id
INNER JOIN msdb.dbo.backupset l
ON
l.database_name=d.name
INNER JOIN msdb.dbo.backupmediafamily k
ON
l.media_set_id=k.media_set_id
ORDER BY
d.name