保存数据的表
create table dc_DiskDetaill
(
Drive varchar(10),
Size varchar(50),
FreeSize varchar(50),
GetTime datetime default(getdate())
)
采集的存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <DBA>
-- Create date: <2012-11-16>
-- Description: <get_disk_size>
-- =============================================
CREATE PROCEDURE [dbo].[Usp_Get_DiskSize]
as
begin
create table #freedisk (drive varchar(50),disksize varchar(100))
insert into #freedisk
exec master.dbo.xp_fixeddrives
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
create table #a (DiskName varchar(50))
insert into #a
exec xp_cmdshell 'wmic LOGICALDISK get name'
select IDENTITY(int,1,1) as id ,DiskName into #a1 from #a
drop table #a
create table #b (freespace varchar(50))
insert into #b
exec xp_cmdshell 'wmic LOGICALDISK get freespace'
select IDENTITY(int,1,1) as id ,freespace into #b1 from #b
drop table #b
create table #c(size varchar(50))
insert into #c
exec xp_cmdshell 'wmic LOGICALDISK get size'
select IDENTITY(int,1,1)as id,size into #c1 from #c
drop table #c
delete from #a1 where id=1
delete from #b1 where id=1
delete from #c1 where id=1
update #a1 set DiskName=REPLACE(DiskName,':','')
update #a1 set DiskName=REPLACE(DiskName,' ','')
select id,LEFT(diskname,1) as diskname into #a2 from #a1
drop table #a1
select a.diskname,b.freespace,c.size,getdate() as gettime into #all
from #a2 as a ,#b1 as b ,#c1 as c where a.id=b.id and a.id=c.id
and a.diskname in (select drive from #freedisk)
drop table #a2
drop table #b1
drop table #c1
update #all set freespace=REPLACE(freespace,' ',''),size=REPLACE(size,' ','')
select diskname,left(freespace,LEN(freespace)-1) as freespace, left(size,LEN(size)-1) as size,gettime into #all2 from #all
drop table #all
insert into dc_DiskDetaill(Drive,Size,FreeSize,GetTime)
select diskname,cast(size as bigint)/1024/1024 as size,cast(freespace as bigint)/1024/1024 as freesize,gettime from #all2
drop table #all2
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
drop table #freedisk
end
调用存储过程并查看是否有数据
exec [Usp_Get_DiskSize]
select * from dc_DiskDetaill
create table dc_DiskDetaill
(
Drive varchar(10),
Size varchar(50),
FreeSize varchar(50),
GetTime datetime default(getdate())
)
采集的存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <DBA>
-- Create date: <2012-11-16>
-- Description: <get_disk_size>
-- =============================================
CREATE PROCEDURE [dbo].[Usp_Get_DiskSize]
as
begin
create table #freedisk (drive varchar(50),disksize varchar(100))
insert into #freedisk
exec master.dbo.xp_fixeddrives
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
create table #a (DiskName varchar(50))
insert into #a
exec xp_cmdshell 'wmic LOGICALDISK get name'
select IDENTITY(int,1,1) as id ,DiskName into #a1 from #a
drop table #a
create table #b (freespace varchar(50))
insert into #b
exec xp_cmdshell 'wmic LOGICALDISK get freespace'
select IDENTITY(int,1,1) as id ,freespace into #b1 from #b
drop table #b
create table #c(size varchar(50))
insert into #c
exec xp_cmdshell 'wmic LOGICALDISK get size'
select IDENTITY(int,1,1)as id,size into #c1 from #c
drop table #c
delete from #a1 where id=1
delete from #b1 where id=1
delete from #c1 where id=1
update #a1 set DiskName=REPLACE(DiskName,':','')
update #a1 set DiskName=REPLACE(DiskName,' ','')
select id,LEFT(diskname,1) as diskname into #a2 from #a1
drop table #a1
select a.diskname,b.freespace,c.size,getdate() as gettime into #all
from #a2 as a ,#b1 as b ,#c1 as c where a.id=b.id and a.id=c.id
and a.diskname in (select drive from #freedisk)
drop table #a2
drop table #b1
drop table #c1
update #all set freespace=REPLACE(freespace,' ',''),size=REPLACE(size,' ','')
select diskname,left(freespace,LEN(freespace)-1) as freespace, left(size,LEN(size)-1) as size,gettime into #all2 from #all
drop table #all
insert into dc_DiskDetaill(Drive,Size,FreeSize,GetTime)
select diskname,cast(size as bigint)/1024/1024 as size,cast(freespace as bigint)/1024/1024 as freesize,gettime from #all2
drop table #all2
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
drop table #freedisk
end
调用存储过程并查看是否有数据
exec [Usp_Get_DiskSize]
select * from dc_DiskDetaill
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16131092/viewspace-2146854/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16131092/viewspace-2146854/