【IT168技术文档】

　　很多朋友问到怎么样通过编写脚本来查询SQL Server的盘区。

　　其实这个并不是很难，下面可以参考一下代码就会大概明白了。

use master
GO
create proc sp_displayextentinfo
@tables char(4) = 'all', @sort char(5) = 'name'
as
set nocount on
declare @dbname sysname,
@tableid int
set @dbname = db_name()
if @tables <> 'user' and @tables <> 'all'
begin
raiserror ('1st Parameter must be All or User', 16, 1)
return
end
if @sort <> 'name' and @sort <> 'page'
begin
raiserror ('2st Parameter must be Name or Page', 16, 1)
return
end
Create table #Extents
(fileid int,
pageid int,
pg_alloc int,
ext_size int,
obj_id int,
index_id int,
pfs_bytes binary(20))
Insert #extents (fileid, pageid, pg_alloc, ext_size, obj_id, index_id, pfs_bytes)
EXEC ('DBCC EXTENTINFO (' + @dbname +') WITH NO_INFOMSGS')
If @tables = 'all'
set @tableid = 0 -- Display all system and user tables
If @tables = 'user'
set @tableid = 99 -- Display only user tables
select fileid, pageid, pg_alloc, ext_size,
object_name(obj_id) obj_name, index_id,
case index_id when 0 then 'Heap'
when 1 then 'Clustered Index'
when 255 then 'Text/Image'
else 'Non-Clustered Index'
end as 'Type'
from #extents
where obj_id > @tableid
order by case when @sort = 'name' then object_name(obj_id) else cast(fileid as sysname) end,
case when @sort = 'name' then index_id else pageid end
GO