May 29, 2012

sp_MSscriptdb_worker (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSscriptdb_worker()

MetaData:

 create procedure sys.sp_MSscriptdb_worker  
as

create table #tempFG
(
cDefault int, -- 1 for default FG, 0 for user defined --
cDBFile int, -- 1 for DB file, 0 for Log file --
cSize int, -- in 8K page --
cMaxSize int,
cGrowth int,
cGrowthType int, -- 1 for GrowthInMB, 0 for GrowthInPercent --
cFGName nvarchar(132) COLLATE database_default NOT NULL, -- FG name --
cName nvarchar(132) COLLATE database_default NOT NULL, -- Logical --
cFileName nvarchar(264) COLLATE database_default NOT NULL, -- Physical --
)

create table #tempID
(
cGroupID int
)

set nocount on

declare @PageSize int;
select @PageSize = (low/1024) from master..spt_values where number = 1 and type = N'E'

-- Default FileGroup first, which should cover all the log files --
-- This one to pick up all the db files in Primary file group, while group id = 1 --
insert #tempFG select 1, 1, convert(int, ceiling(((convert(float, o.size) * @PageSize)/1024))), (case when (o.maxsize < 1) then o.maxsize else convert(int, ceiling(((convert(float, o.maxsize) * @PageSize)/1024))) end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), g.groupname, RTRIM(o.name), RTRIM(o.filename) from dbo.sysfiles o, dbo.sysfilegroups g where g.groupid = 1 and g.groupid = o.groupid and (o.status & 0x40) = 0
-- This one to pick up all the log files in Primary file group, while group id = 0, note that group id 0 does not exist in sysfilegroups --
insert #tempFG select 1, 0, (o.size * @PageSize)/1024, (case when (o.maxsize < 1) then o.maxsize else convert(int, ceiling(((convert(float, o.maxsize) * @PageSize)/1024))) end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), N'PRIMARY', RTRIM(o.name), RTRIM(o.filename) from dbo.sysfiles o where o.groupid = 0 and (o.status & 0x40) <> 0
-- Other FileGroups, we should have DBFiles, no log files --

insert #tempID select groupid from dbo.sysfilegroups where groupid <> 1

declare @FGid int
exec(N'declare hC cursor global for select cGroupID from #tempID')
open hC
fetch hC into @FGid
while (@@fetch_status >= 0) begin
insert #tempFG select 0, 1, (o.size * @PageSize)/1024, (case when (o.maxsize < 1) then o.maxsize else convert(int, ceiling(((convert(float, o.maxsize) * @PageSize)/1024))) end), o.growth, (case when (o.status & 0x100000 = 0) then 1 else 0 end), g.groupname, RTRIM(o.name), RTRIM(o.filename) from dbo.sysfiles o, dbo.sysfilegroups g where g.groupid = @FGid and g.groupid = o.groupid and (o.status & 0x40) = 0
fetch hC into @FGid
end
deallocate hC

select * from #tempFG
DROP TABLE #tempFG

No comments:

Post a Comment

Total Pageviews