MS SQLServer 批量附加数据库

 ************************************************************ * 标题：MS SQLServer 批量附加数据库 * 说明：请根据下面的注释使用此脚本 * 时间: 2015/7/13 11:16:41 ************************************************************/USE MASTERGOIF OBJECT_ID('[sp_AttchDataBase]') IS NOT NULL    DROP PROCEDURE [sp_AttchDataBase]GO/*附加数据库(V2.0) Andy 2011-7-8 */CREATE PROCEDURE sp_AttchDataBase(    @Path       NVARCHAR(1024),    @DataFiles  NVARCHAR(MAX) = NULL,    @SplitStr   NVARCHAR(50) = ',')ASSET NOCOUNT ON/*V2.0 版本，在V1.0基础上，处理文件路径不规范原則，e.g. @DataFiles='E:\"my data DB"\"Hello RT"'@Path文件路径@DataFiles文件名列表@SplitStr文件名列表中的文件分隔符1.必须把要附加的数据库文件(*.mdf和*.ldf)放到@Path下，2.当@DataFiles Is Null 会附加@Path文件夹下的所有数据库文件.e.g:Exec sp_AttchDataBase 'D:\SQL2005\DE2\'*/--检查文件路径是否正确DECLARE @Dir  NVARCHAR(1024),        @i    INT,        @x    XMLIF RIGHT(@Path, 1) <> '\'    SET @Path = @Path + '\'IF CHARINDEX('\\', @Path) > 0BEGIN    RAISERROR 50001 N'文件路径中不能包含有"\\",@Path设置错误.'    RETURN(1)ENDSET @Dir = 'Dir ' + @PathEXEC @i = xp_cmdshell @Dir,     no_outputIF @i <> 0BEGIN    RAISERROR 50001 N'无效的文件路径,@Path设置错误.'    RETURN(1)ENDSET @Path = REPLACE(@Path, '"', '') /*处理文件路径不规范原則*/DECLARE @Files               TABLE(NAME NVARCHAR(512))DECLARE @filetmpfin          TABLE(            NAME NVARCHAR(255) NOT NULL,            depth INT NULL,            IsFile BIT NULL        )DECLARE @SmoPrimayChildren   TABLE(            STATUS INT,            fileid INT,            NAME SYSNAME,            FILENAME NVARCHAR(512)        )DECLARE @smoPrimaryFileProp  TABLE(PROPERTY SQL_VARIANT NULL, VALUE SQL_VARIANT NULL)SET @DataFiles = REPLACE(        REPLACE(REPLACE(@DataFiles, CHAR(13) + CHAR(10), ''), CHAR(13), ''),        CHAR(10),        ''    )SET @x = N'<Root><File>' + REPLACE(@DataFiles, @SplitStr, N'</File><File>') +     N'</File></Root>'INSERT INTO @FilesSELECT t.v.value('.[1]', 'nvarchar(512)') AS NAMEFROM   @x.nodes('Root/File') t(v)WHERE  t.v.value('.[1]', 'nvarchar(512)') > ''INSERT INTO @filetmpfinEXEC MASTER.dbo.xp_dirtree @Path,     1,     1DECLARE @File      NVARCHAR(255),        @sql       NVARCHAR(4000),        @DataBase  SYSNAMEDECLARE cur_File   CURSOR  FOR    SELECT NAME    FROM   @filetmpfin AS a    WHERE  IsFile = 1           AND NAME LIKE '%.mdf'           AND (                   EXISTS(                       SELECT 1                       FROM   @Files                       WHERE  NAME = a.Name                   )                   OR @DataFiles IS NULL               )           AND NOT EXISTS(                   SELECT 1                   FROM   MASTER.sys.master_files                   WHERE  physical_name = @Path + a.Name               )OPEN cur_FileBEGIN TRYFETCH NEXT FROM cur_File INTO @FileWHILE @@Fetch_Status = 0BEGIN    SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''        ' , 2) With No_Infomsgs'        INSERT INTO @smoPrimaryFileProp    EXEC (@sql)        SET @sql = 'dbcc checkprimaryfile (N''' + @Path + @File + ''        ' , 3) With No_Infomsgs'        INSERT INTO @SmoPrimayChildren    EXEC (@sql)        SELECT @DataBase = QUOTENAME(CONVERT(NVARCHAR(255), VALUE)),           @sql = NULL    FROM   @smoPrimaryFileProp    WHERE  CONVERT(NVARCHAR(255), PROPERTY) = 'Database name'        SELECT @sql = ISNULL(               @sql + ',' + CHAR(13) + CHAR(10),               'Create DataBase ' + @DataBase + ' On' + CHAR(13) + CHAR(10)           ) +           '(FileName=N''' + @Path + RIGHT(               RTRIM(FILENAME),               CHARINDEX('\', REVERSE(RTRIM(FILENAME))) -1           ) + ''')'    FROM   @SmoPrimayChildren        EXEC (@sql + ' For Attach')        PRINT N'成功附加数据库: ' + @DataBase        DELETE     FROM   @SmoPrimayChildren        DELETE     FROM   @smoPrimaryFileProp        FETCH NEXT FROM cur_File INTO @FileENDEND TRYBEGIN CATCHDECLARE @Error NVARCHAR(2047)SET @Error = ERROR_MESSAGE()RAISERROR 50001 @ErrorEND CATCHCLOSE cur_FileDEALLOCATE cur_FileGO/************************************************************ * 调用方式 ************************************************************/--use master--Go --Exec sp_AttchDataBase --        @Path = 'E:\100.其他\测试', -- nvarchar(1024)--        @DataFiles = NULL, -- nvarchar(max)--        @SplitStr = NULL -- nvarchar(50)