动态创建有规则的文件夹

开发环境:MS SQL2005,windows2003

应用场景:大多数的SQL Server数据库管理员在整理其磁盘驱动器的数据库文件时都有各自特定的标准。是否可以创建有规则的文件夹呢?

解决之道:

USE Master
GO

SET NOCOUNT ON

-- 1 - Variable declaration
DECLARE @DBName sysname
DECLARE @DataPath nvarchar(500)
DECLARE @LogPath nvarchar(500)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)

-- 2 - Initialize variables
SET @DBName = 'Foo'
SET @DataPath = 'C:\zTest1\' + @DBName
SET @LogPath = 'C:\zTest2\' + @DBName

-- 3 - @DataPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @DataPath

-- 4 - Create the @DataPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @DataPath

-- 5 - Remove all records from @DirTree
DELETE FROM @DirTree

-- 6 - @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath

-- 7 - Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath

SET NOCOUNT OFF

GO

以上脚本的核心功能是基于两个系统扩展存储过程：master.sys.xp_dirtree和master.sys.xp_create_subdir的功能。我们可以对这两个存储过程分别说明：

• master.sys.xp_dirtree - 这个扩展存储过程会返回作为参数传递进存储过程的文件夹里的所有文件夹。它同时也会返回它查找到的每个文件夹的嵌套关系。通过把xp_dirtree返回的值插入到临时表中，你就可以用它查询测试你要创建的文件夹是否存在。
• master.sys.xp_create_subdir – 可以使用这个存储过程在本地服务器或共享服务器里创建一个文件夹。

如果你需要为新建数据库创建目录，创建文件备份或者自定义ETL过程，都可以考虑使用这个脚本。

如果你需要执行一系列统一的过程，可以考虑把代码转化成一个存储过程，只要把代码中的变量改成可以传递进存储过程的参数就可以。