sqlserver的日常检查脚本归结

核心提示：declare @Host_Name varchar(100)declare @CPU varchar(3)declare @nodes nvarchar(400)set @nodes =null /* We are not able to trap the node names for SQL Server 2000

declare @Host_Name varchar(100)

declare @CPU varchar(3)

declare @nodes nvarchar(400)

set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/

declare @mirroring varchar(15)

set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/

Declare @reg_node1 varchar(100)

Declare @reg_node2 varchar(100)

Declare @reg_node3 varchar(100)

Declare @reg_node4 varchar(100)

SET @reg_node1 = N'Cluster\Nodes\1'

SET @reg_node2 = N'Cluster\Nodes\2'

SET @reg_node3 = N'Cluster\Nodes\3'

SET @reg_node4 = N'Cluster\Nodes\4'

Declare @image_path1 varchar(100)

Declare @image_path2 varchar(100)

Declare @image_path3 varchar(100)

Declare @image_path4 varchar(100)

set @image_path1=null

set @image_path2=null

set @image_path3=null

set @image_path4=null

Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT

Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT

Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT

Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)                        

drop table nodes

Create table nodes (name varchar (20))

 insert into nodes values (@image_path1)

 insert into nodes values (@image_path2)

 insert into nodes values (@image_path3)

 insert into nodes values (@image_path4)

 --declare @Out nvarchar(400)                        

 --declare @value nvarchar (20)

 SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null

-- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.

SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');

IF @instance_name!='MSSQLSERVER'

BEGIN

set @system_instance_name=@instance_name

set @instance_name='MSSQL$'+@instance_name

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

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 + '\Setup';