Hi All,
Here by I am presending a small Procedure which will collect all necessary information about from a connected instance. We can use this procedure to get SQL Server Details in our organisation.
Currently this will work for SQL 2005 & SQL 2008 instances only. Soon will post for SQL 2000.
*********************************
* SQL Server Inventory
* By Gurpreet Sethi
* Will Run On SQL Server 2005
*********************************
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SQLInventory] as
/*First -> Declaring all the variables*/
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)
declare @registry_key1 varchar(300)
declare @registry_key2 varchar(300)
declare @IpAddress varchar(20)
declare @domain varchar(50)
declare @cluster int
/*Second -> Setting the instance name to Default or named*/
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
if @instance_name!='MSSQLSERVER'
set @instance_name='MSSQL$'+@instance_name
/*Third -> getting the system instance name as we were using the service instance name
(Service is : MSSQL$ and system is MSSQL.)*/
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;
/*Fourth -> Setting the variables, getting system SQL Server name And setting the instance name to default or named*/
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';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';
SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';
/*Fifth -> Getting the values of SQL Binaries path , Startup type,Startup service account name Errorlog directory and IP address*/
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
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain 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)
declare @Out varchar(8000)
SELECT @Out = COALESCE(@Out+'' ,'') + Nodename
from sys.dm_os_cluster_nodes
/*Sixth -> Get Server Details*/
SELECT
@domain as 'Domain',
serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine Name',
CPU_COUNT as 'CPU Count',
hyperthread_ratio as 'Hyper Thread Ratio',
CPU_COUNT/hyperthread_ratio as 'Physical CPU',
physical_memory_in_bytes / 1048576 as 'Physical Memory (MB)',
virtual_memory_in_bytes / 1048576 as 'virtual_mem_MB',
max_workers_count as 'Max Worker Thread Count',
os_error_mode as 'OS Error Mode',
os_priority_class as 'OS Priority Class',
serverproperty ('Instancename') as 'Instance Name',
@Ipaddress as 'IP Address',
@image_path as 'Binaries Path',
@log_directory as 'Error Logs Location',
@start_username as 'Statrtup User',
@Startuptype as 'startup_type',
serverproperty('productlevel') as ServicePack,
serverproperty('edition') as edition,
serverproperty('productversion') as version,
serverproperty('collation') as collation,
serverproperty('Isclustered') as IS_Clustered,
@out as 'Cluster Nodes',
serverproperty('IsFullTextInstalled') as IS_FullText
From
sys.dm_os_sys_info
/*Seventh -> Get Database Details Secondly */
SELECT
serverproperty ('ComputerNamePhysicalNetBIOS') as machine,
serverproperty ('Instancename') as instance_name,
(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
,d.is_published as Published
,d.is_subscribed as Subscribed
,d.is_distributor as Distributor
,m.mirroring_state
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
ORDER BY
d.name
/* Eigth -> Select backup Details */
select distinct
b.machine_name as 'Server Name',
b.server_name as 'Instance Name',
b.database_name,
d.database_id ,
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType
from sys.databases d inner join msdb.dbo.backupset b
On b.database_name =d.name
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
EXEC [dbo].[SQLINVENTORY]
Regards
GURSETHI
&
ABHAYC
