sqlserver的日常检查脚本归结

核心提示：DROP TABLE tmpcreate table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value i

DROP TABLE tmp

create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))

insert into tmp([index],name,internal_value,character_value) exec xp_msver PhysicalMemory

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

drop table ipadd

create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))

insert into ipadd (IP)exec xp_cmdshell 'ipconfig'

delete from ipadd where ip not like '%IP Address.%' or IP is null

-- Step 11 : Getting the Server details 

SELECT  top 1              

@domain as 'Domain',                      

serverproperty('Machinename') as 'MachineName',                      

@CPU as 'CPUCount',

cast (t.internal_value as bigint) as PhysicalMemoryMB,

cast(substring ( I.IP , 44,41) as nvarchar(20))as IP_Address,

serverproperty('Instancename') as 'InstanceName',                      

@image_path as 'BinariesPath',                      

@log_directory as 'ErrorLogsLocation',                      

@start_username as 'StartupUser',                      

@Startuptype as 'StartupType',                      

serverproperty('Productlevel') as 'ServicePack',                      

serverproperty('edition') as 'Edition',                      

serverproperty('productversion') as 'Version',                      

serverproperty('collation') as 'Collation',                      

serverproperty('Isclustered') as 'ISClustered',                      

@Out as 'ClustreNodes',

serverproperty('IsFullTextInstalled') as 'ISFullText'                     　

From tmp t inner join IPAdd I

on t.server = I.server

--Step 12 : Getting the instance details 

SELECT