Recently I had to upgrade a larger database server with 167 databases on it from SQL 2000 Enterprise Edition to SQL 2005 Standard. I had to maintain a fast method of attaching the databases to 2005 or 2000 in the event of an issue, rapidly, so I needed generate a script to attach/detach every database on the server.
SET NOCOUNT ON
CREATE TABLE#Temp
(AttachScript varchar(8000))
DECLARE@sql varchar(4000)
DECLARE@dbname varchar(128)
SELECT@dbname =min(name)
FROMmaster..sysdatabases
WHILE@dbname IS NOT NULL
BEGIN
SET@sql =' use ['+@dbname +']
declare @res varchar(8000)
set nocount on
set @res = ''exec sp_attach_db N'''''+@dbname+''''' ''
select @res = @res + '','''''' + rtrim(cast(filename as varchar(100))) + ''''''''
from ['+@dbname+']..sysfiles
order by fileid asc
select @res
'
INSERT INTO#Temp
EXEC(@sql)
SELECT@dbname =min(name)
FROMmaster..sysdatabases
WHERE Name>@dbname
END
SELECT* FROM #Temp
DROP TABLE #Temp
No comments:
Post a Comment