增加文件组和文件的封装

增加文件组:P_Task_AddFileGroup
增加文件到文件组:P_Task_AddFileToFileGroup
删除文件:P_Task_RemoveFile
删除文件组:P_Task_RemoveFileGroup

 

/*有用的SQL,*/
1.select * from [dbo].[sysfiles]
SELECT df.[name], df.physical_name, df.[size], df.growth, f.[name]
　　[filegroup], f.is_default
　　FROM sys.database_files df
　　JOIN sys.filegroups f
　　ON df.data_space_id = f.data_space_id

SELECT * from sys.filegroups

USE [Test]
GO

/****** Object:  StoredProcedure [dbo].[P_Task_AddFileGroup]    Script Date: 4/29/2016 17:13:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- [P_Task_Add_FileGroup] 'dd1d'
-- =============================================
CREATE PROCEDURE [dbo].[P_Task_AddFileGroup]
	-- Add the parameters for the stored procedure here

	@fileGroupName varchar(50)
	
AS
BEGIN
		declare @DB_NAME varchar(50);
		declare @sql varchar(500);
		set @DB_NAME = DB_NAME();
		if exists(SELECT 1 from sys.filegroups where name=@fileGroupName)
		begin
			print 'exists'
			return;
		end
		set @sql = '
		ALTER DATABASE '+@DB_NAME+' ADD FILEGROUP  '+@fileGroupName;--新建立的文件组

		print @sql
		exec(@sql)
		/*/*select * from [dbo].[sysfiles]
	SELECT df.[name], df.physical_name, df.[size], df.growth, f.[name]
　　[filegroup], f.is_default
　　FROM sys.database_files df
　　JOIN sys.filegroups f
　　ON df.data_space_id = f.data_space_id

SELECT * from sys.filegroups
*/*/
		
END



GO

/****** Object:  StoredProcedure [dbo].[P_Task_AddFileToFileGroup]    Script Date: 4/29/2016 17:13:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- P_Task_AddFileToFileGroup 'dd1d','dd1d','d:\'
-- =============================================
CREATE PROCEDURE [dbo].[P_Task_AddFileToFileGroup]
	-- Add the parameters for the stored procedure here
	@filename varchar(50),
	@fileGroupName varchar(50),
	@path varchar(100)--
AS
BEGIN
		declare @DB_NAME varchar(50);
		declare @sql varchar(500);
		set @DB_NAME = DB_NAME();
		if exists(SELECT 1 from  [dbo].[sysfiles] where name=@filename)
		begin
			print 'exists'
			return;
		end
		set @sql = '
		ALTER DATABASE '+@DB_NAME +'
		ADD FILE 
		(
			NAME = '''+@filename +''',
			FILENAME = N'''+@path+@filename+'.ndf'',
			SIZE = 5MB,
			MAXSIZE = 100MB,
			FILEGROWTH = 5MB
		)
		TO FILEGROUP '+@fileGroupName;--新建立的文件组

		print @sql
		exec(@sql)
		
END



GO

/****** Object:  StoredProcedure [dbo].[P_Task_RemoveFile]    Script Date: 4/29/2016 17:13:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
--[dbo].[P_Task_Add_FileGroup] 'ddd'
--[dbo].[P_Task_AddFileToFileGroup]'G2222','ddd','d:\'
-- [P_Task_RemoveFile] 'dd1d'
-- =============================================
CREATE PROCEDURE [dbo].[P_Task_RemoveFile]
	-- Add the parameters for the stored procedure here
	@fileName varchar(100)
AS
BEGIN
	declare @DB_NAME varchar(50);
	declare @sql varchar(500);
	set @DB_NAME = DB_NAME();
	if not exists(SELECT 1 from sysfiles where name=@fileName)
	begin
		print 'not exists'
		return;
	end
	set @sql = 'ALTER DATABASE '+@DB_NAME+	' remove FILE '+@fileName;
	print @sql
	exec( @sql)

	/*select * from [dbo].[sysfiles]
	SELECT df.[name], df.physical_name, df.[size], df.growth, f.[name]
　　[filegroup], f.is_default
　　FROM sys.database_files df
　　JOIN sys.filegroups f
　　ON df.data_space_id = f.data_space_id

SELECT * from sys.filegroups*/
END

GO

/****** Object:  StoredProcedure [dbo].[P_Task_RemoveFileGroup]    Script Date: 4/29/2016 17:13:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
--[dbo].[P_Task_Add_FileGroup] 'ddd'
--P_Task_RemoveFileGroup 'ddd'
-- =============================================
CREATE PROCEDURE [dbo].[P_Task_RemoveFileGroup]
	-- Add the parameters for the stored procedure here
	@fileGroupName varchar(100)
AS
BEGIN
	declare @DB_NAME varchar(50);
	declare @sql varchar(500);
	set @DB_NAME = DB_NAME();
	if not exists(SELECT 1 from sys.filegroups where name=@fileGroupName)
	begin
		print 'not exists'
		return;
	end
	set @sql = 'ALTER DATABASE '+@DB_NAME+	' remove FILEGROUP '+@fileGroupName;
	print @sql
	exec( @sql)

	/*select * from [dbo].[sysfiles]
	SELECT df.[name], df.physical_name, df.[size], df.growth, f.[name]
　　[filegroup], f.is_default
　　FROM sys.database_files df
　　JOIN sys.filegroups f
　　ON df.data_space_id = f.data_space_id

SELECT * from sys.filegroups*/
END

GO