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

标签： ms sql server数据库