Cacher is the code snippet organizer for pro developers

-- ============================================================================
-- Author			 : Andrei STEFAN
-- Module			 : Application Monitoring Tool
-- ============================================================================
DECLARE @dbName		[sysname],
		@dbid		[int],
		@fileName	[sysname],
		@fileType	[int],
		@fileid		[int],
		@sqlScript	[nvarchar](1024)
		
DECLARE crsDBList CURSOR FOR	SELECT [name], [database_id] FROM sys.databases 
								WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb')
								ORDER BY [name]
OPEN crsDBList
FETCH NEXT FROM crsDBList INTO @dbName, @dbid
WHILE @@FETCH_STATUS=0
	begin
		SET @sqlScript = 'EXEC sp_detach_db [' + @dbName + ']'
		PRINT @sqlScript
		PRINT 'GO'
	
		SET @sqlScript = N'CREATE DATABASE [' + @dbName + '] ON '

		DECLARE crsDBFileList CURSOR FOR	SELECT [physical_name], [type], [file_id] FROM sys.master_files
											WHERE [database_id]=@dbid
											ORDER BY [type], [file_id]
		OPEN crsDBFileList
		FETCH NEXT FROM crsDBFileList INTO @fileName, @fileType,@fileid
		WHILE @@FETCH_STATUS=0
			begin
				SET @sqlScript = @sqlScript + N'(FILENAME = ''' + @fileName + '''),'
				FETCH NEXT FROM crsDBFileList INTO @fileName, @fileType,@fileid
			end
		CLOSE crsDBFileList
		DEALLOCATE crsDBFileList
		
		SET @sqlScript=SUBSTRING(@sqlScript, 1, LEN(@sqlScript)-1)
		SET @sqlScript = @sqlScript + ' FOR ATTACH;'
		PRINT @sqlScript
		PRINT 'GO'
		 
		FETCH NEXT FROM crsDBList INTO @dbName, @dbid
	end
CLOSE crsDBList
DEALLOCATE crsDBList
			

-- Build the sp_attach_db: 
-- (I preach everyone against using cursor... so I don't) 
SET NOCOUNT ON  
DECLARE     @cmd        VARCHAR(MAX), 
            @dbname     VARCHAR(200), 
            @prevdbname VARCHAR(200) 

SELECT @cmd = '', @dbname = ';', @prevdbname = '' 

CREATE TABLE #Attach 
    (Seq        INT IDENTITY(1,1) PRIMARY KEY, 
     dbname     SYSNAME NULL, 
     fileid     INT NULL, 
     filename   VARCHAR(1000) NULL, 
     TxtAttach  VARCHAR(MAX) NULL 
) 

INSERT INTO #Attach 
SELECT DISTINCT DB_NAME(dbid) AS dbname, fileid, filename, CONVERT(VARCHAR(MAX),'') AS TxtAttach 
FROM master.dbo.sysaltfiles 
WHERE dbid IN (SELECT dbid FROM master.dbo.sysaltfiles  
            WHERE SUBSTRING(filename,1,1) IN ('E','F')) 
            AND DATABASEPROPERTYEX( DB_NAME(dbid) , 'Status' ) = 'ONLINE' 
            AND DB_NAME(dbid) NOT IN ('master','tempdb','msdb','model') 
ORDER BY dbname, fileid, filename 

UPDATE #Attach 
SET @cmd = TxtAttach =   
            CASE WHEN dbname <> @prevdbname  
            THEN CONVERT(VARCHAR(200),'exec sp_attach_db @dbname = N''' + dbname + '''') 
            ELSE @cmd 
            END +',@filename' + CONVERT(VARCHAR(10),fileid) + '=N''' + filename +'''', 
    @prevdbname = CASE WHEN dbname <> @prevdbname THEN dbname ELSE @prevdbname END, 
    @dbname = dbname 
FROM #Attach  WITH (INDEX(0),TABLOCKX) 
 OPTION (MAXDOP 1) 

SELECT TxtAttach 
FROM 
(SELECT dbname, MAX(TxtAttach) AS TxtAttach FROM #Attach  
 GROUP BY dbname) AS x 

DROP TABLE #Attach 
GO 

DECLARE @SqlTxt nvarchar(max)
DECLARE @DbName varchar(255)
DECLARE @RowNo INT
DECLARE @JobName VARCHAR(255)
DECLARE FileCursor CURSOR LOCAL FORWARD_ONLY FOR
	SELECT name FROM sys.databases 
	WHERE name like 'pcds%' or name like 'scds%' or name like 'dcds%'	
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
	-- SELECT @DbName
	SET @SqlTxt  = 'EXEC master.dbo.killConnectionsToDb @DBName = ''' + @DbName + ''' ; sp_detach_db @dbname= ''' + @DbName + ''',  @skipchecks=  ''skipchecks''; '
	PRINT @SqlTxt
	-- EXEC sp_executesql @SqlTxt
	FETCH NEXT FROM FileCursor INTO @DbName
END
CLOSE FileCursor
DEALLOCATE FileCursor