创建文件与文件组

IF object_id('fn_nums') IS NOT NULL
BEGIN 
	PRINT '--Dropping function fn_nums'
	DROP FUNCTION fn_nums
	IF @@ERROR = 0 PRINT '--Function fn_nums dropped'
END
go

CREATE FUNCTION fn_nums
(
	@n AS BIGINT
) 
RETURNS TABLE
	AS 
RETURN
WITH
	L0 AS (SELECT 1 AS c UNION ALL SELECT 1),
	L1 AS (SELECT 1 AS c FROM L0 AS a,L0 AS b),
	L2 AS (SELECT 1 AS c FROM L1 AS a,L1 AS b),
	L3 AS (SELECT 1 AS c FROM L2 AS a,L2 AS b),
	L4 AS (SELECT 1 AS c FROM L3 AS a,L3 AS b),
	L5 AS (SELECT 1 AS c FROM L4 AS a,L4 AS b), 
	nums AS (SELECT ROW_NUMBER() OVER (ORDER BY c)AS n FROM L5)
SELECT N FROM nums WHERE n<=@n
GO
--SELECT * FROM  dbo.fn_nums(10)
DECLARE @dbname NVARCHAR(55)
DECLARE @path NVARCHAR(500)
DECLARE @groupfix NVARCHAR(50)
DECLARE @filefix NVARCHAR(50)
DECLARE @groups INT
DECLARE @fileofgroups INT

DECLARE @i INT
SET @dbname='tt'
SET @path = 'D:\data'
SET @groupfix = 'FG_Archive_Id_0'
SET @filefix = '_File0'
SET @i = 0
DECLARE cur CURSOR STATIC FORWARD_ONLY FOR
SELECT N FROM  dbo.fn_nums(@groups)
OPEN cur
BEGIN
	DECLARE @sql NVARCHAR(max)
	FETCH NEXT FROM cur INTO @i
	WHILE @@FETCH_STATUS=0
	BEGIN
		 DECLARE @c INT
		 SET @c=1
		 
         PRINT '---文件组 [ '+@groupfix+CAST(@i AS NVARCHAR(2))+']开始'
		SET  @sql= 'ALTER DATABASE ['+@dbname+'] ADD FILEGROUP ['+@groupfix+CAST(@i AS NVARCHAR(2))+']'
		 PRINT @sql
		 EXEC(@sql)
         PRINT 'go'
         
         WHILE @c<=@fileofgroups
         BEGIN
         print	'-----文件 [ '+@filefix +CAST(@c AS NVARCHAR(2))+']开始'
         	 SET @sql= 'ALTER DATABASE ['+@dbname+'] ADD FILE( NAME = N'''+@groupfix+CAST(@i AS NVARCHAR(2))+'_'+@filefix+CAST(@c AS NVARCHAR(2))+''',FILENAME = 
                                  N'''+@path+'\'+@groupfix+CAST(@i AS NVARCHAR(2))+@filefix +CAST(@c AS NVARCHAR(2))+'.ndf'',
                                  SIZE = 3072KB,
                                  FILEGROWTH = 1024KB
                              ) TO FILEGROUP ['+@groupfix+CAST(@i AS NVARCHAR(2))+']'
            PRINT 'go'
             PRINT @sql
		 EXEC(@sql)
        PRINT '-----文件 [ '+@filefix +CAST(@c AS NVARCHAR(2))+']结束'
          PRINT ''
         	SET @c=@c+1
         END
      PRINT '---文件组 [ '+@groupfix+CAST(@i AS NVARCHAR(2))+']结束'
             PRINT ''
		 FETCH NEXT FROM cur INTO @i
	END
END
CLOSE cur
DEALLOCATE cur
GO
 SELECT  * from sys.filegroups