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