1 ---读取源数据库信息 先用从源数据库读取数据库信息
 2 use master
 3 IF OBJECT_ID(‘sourcetable‘) IS NOT NULL
 4     DROP TABLE sourcetable;
 5 SELECT name,
 6     database_id,
 7     0 AS okflag
 8 INTO sourcetable
 9 FROM sys.databases
10 WHERE database_id > 4
11 ORDER BY name;

  1 /********
  2 Just for a quick review, xp_dirtree has three parameters:
  3 1 directory - This is the directory you pass when you call the stored procedure; for example ‘D:\Backup‘.
  4 2 depth  - This tells the stored procedure how many subfolder levels to display.  The default of 0 will display all subfolders.
  5 3 isfile - This will either display files as well as each folder.  The default of 0 will not display any files.
  6 *********/
  7 ---读取数据库文件
  8 IF OBJECT_ID(‘tempdb..#DirectoryTree‘) IS NOT NULL
  9     DROP TABLE #DirectoryTree;
 10 CREATE TABLE #DirectoryTree
 11 (
 12     id INT IDENTITY(1, 1),
 13     subdirectory NVARCHAR(512),
 14     depth INT,
 15     isfile BIT
 16 );
 17 INSERT #DirectoryTree
 18 (   subdirectory,
 19     depth,
 20     isfile
 21 )
 22 EXEC master..xp_dirtree ‘D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA‘,  --数据库文件存放路径，如有多个路径类似。
 23     1,
 24     1;
 25 --SELECT *
 26 --FROM #DirectoryTree;
 27 ---生成附加代码
 28 DECLARE @file VARCHAR(MAX);
 29 SET @file
 30     = ‘D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\‘; ---具体数据文件存放的路径
 31 DECLARE @name VARCHAR(500),  --数据库名
 32     @database_id INT,        --数据库ID
 33     @temp VARCHAR(MAX);      --存放附加代码
 34 SET @temp = ‘‘;
 35 DECLARE c_wen CURSOR FAST_FORWARD
 36 FOR
 37 SELECT name,
 38     database_id
 39 FROM sourcetable
 40 ORDER BY name;
 41 OPEN c_wen;
 42 FETCH NEXT FROM c_wen
 43 INTO @name,
 44     @database_id;
 45 WHILE @@FETCH_STATUS = 0
 46 BEGIN
 47     DECLARE @id INT,        --存放附加文件个数值
 48         @temp_id INT,       --存放最大附加文件个数值
 49         @subdirectory VARCHAR(MAX), --待附加的文件名
 50         @t VARCHAR(MAX);    --存放单个库附加的代码
 51     SET @t = ‘‘;
 52     SELECT @id = COUNT(1)
 53     FROM #DirectoryTree
 54     WHERE subdirectory LIKE ‘%‘ + @name + ‘%‘;
 55     SELECT @temp_id = COUNT(1)
 56     FROM #DirectoryTree
 57     WHERE subdirectory LIKE ‘%‘ + @name + ‘%‘;
 58     SELECT ROW_NUMBER() OVER (ORDER BY subdirectory) id,
 59         subdirectory
 60     FROM #DirectoryTree
 61     WHERE subdirectory LIKE ‘%‘ + @name + ‘%‘;
 62     WHILE (@id) >= 1       --存在多个需附加的文件
 63     BEGIN
 64         SELECT @subdirectory = subdirectory
 65         FROM
 66         (
 67             SELECT ROW_NUMBER() OVER (ORDER BY subdirectory) id,
 68                 subdirectory
 69             FROM #DirectoryTree
 70             WHERE subdirectory LIKE ‘%‘ + @name + ‘%‘
 71         ) a
 72         WHERE a.id = @id;
 73         SELECT @t
 74             = @t + ‘‘‘‘ + @file + @subdirectory + ‘‘‘‘
 75               + CASE
 76                     WHEN @id > 1 THEN
 77                         ‘,‘
 78                     ELSE
 79                         ‘; ‘ + CHAR(10) + CHAR(13) + ‘GO‘
 80                 END + CHAR(10) + CHAR(13);
 81         SET @id = @id - 1;
 82     END;
 83     IF (
 84            @temp_id = 0    --只有库名，不存在附加文件
 85        )
 86     BEGIN
 87         SELECT @t = ‘‘;
 88     END;
 89     ELSE
 90     BEGIN
 91         SELECT @t
 92             = ‘EXEC sys.sp_attach_db ‘ + ‘‘‘‘ + @name + ‘‘‘‘ + ‘,‘ + CHAR(10)
 93               + CHAR(13) + @t;
 94     END;
 95     FETCH NEXT FROM c_wen
 96     INTO @name,
 97         @database_id;
 98     SELECT @temp = @temp + @t;
 99 END;
100 SELECT @temp
101 FOR XML PATH(‘‘);
102 CLOSE c_wen;
103 DEALLOCATE c_wen;