************************************************************

 * 标题：MS SQLServer 批量附加数据库

 * 说明：请根据下面的注释使用此脚本

 * 时间: 2015/7/13 11:16:41

 ************************************************************/

USE MASTER

GO

IF 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) = ','

)

AS

	SET 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    XML

	IF RIGHT(@Path, 1) <> '\'

	    SET @Path = @Path + '\'

	IF CHARINDEX('\\', @Path) > 0

	BEGIN

	    RAISERROR 50001 N'文件路径中不能包含有"\\",@Path设置错误.'

	    RETURN(1)

	END

	SET @Dir = 'Dir ' + @Path

	EXEC @i = xp_cmdshell @Dir,

	     no_output

	IF @i <> 0

	BEGIN

	    RAISERROR 50001 N'无效的文件路径,@Path设置错误.'

	    RETURN(1)

	END

	SET @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 @Files

	SELECT t.v.value('.[1]', 'nvarchar(512)') AS NAME

	FROM   @x.nodes('Root/File') t(v)

	WHERE  t.v.value('.[1]', 'nvarchar(512)') > ''

	INSERT INTO @filetmpfin

	EXEC MASTER.dbo.xp_dirtree @Path,

	     1,

	     1

	DECLARE @File      NVARCHAR(255),

	        @sql       NVARCHAR(4000),

	        @DataBase  SYSNAME

	DECLARE 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_File

	BEGIN TRY

		FETCH NEXT FROM cur_File INTO @File

		WHILE @@Fetch_Status = 0

		BEGIN

		    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 @File

		END

	END TRY

	BEGIN CATCH

		DECLARE @Error NVARCHAR(2047)

		SET @Error = ERROR_MESSAGE()

		RAISERROR 50001 @Error

	END CATCH

	CLOSE cur_File

	DEALLOCATE cur_File

GO

/************************************************************

 * 调用方式

 ************************************************************/

--use master

--Go

--Exec sp_AttchDataBase

--        @Path = 'E:\100.其他\测试', -- nvarchar(1024)

--        @DataFiles = NULL, -- nvarchar(max)

--        @SplitStr = NULL -- nvarchar(50)