??
??USE?MASTER?? GO??
??IF?OBJECT_ID('[sp_AttchDataBase]')?IS?NOT?NULL??
????DROP?PROCEDURE?[sp_AttchDataBase]??GO??
????CREATE?PROCEDURE?sp_AttchDataBase(??
????@Path???????NVARCHAR(1024),??
????@DataFiles??NVARCHAR(MAX)?=?NULL,??
????@SplitStr???NVARCHAR(50)?=?','??)?? AS??
????SET?NOCOUNT?ON??
??????
??????
??????
??????
???? ????DECLARE?@Dir??NVARCHAR(1024),??
????????????@i????INT,??
????????????@x????XML??
??????
????IF?RIGHT(@Path,?1)?<>?'\'??
????????SET?@Path?=?@Path?+?'\'??
??????
????IF?CHARINDEX('\\',?@Path)?>?0??
????BEGIN??
???????? ????????RETURN(1)??
????END??
??????
????SET?@Dir?=?'Dir?'?+?@Path??
????EXEC?@i?=?xp_cmdshell?@Dir,??
?????????no_output??
??????
????IF?@i?<>?0??
????BEGIN??
???????? ????????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''?+?REPLACE(@DataFiles,?@SplitStr,?N'')?+???
????????N''??
??????
??????
????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'成功附加數(shù)據(jù)庫(kù):?'?+?@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()??
???????? ????END?CATCH??
??????
??????
????CLOSE?cur_File??
????DEALLOCATE?cur_File?? GO??
???? ???