增加文件组: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