DECLARE @cmd VARCHAR(MAX), @dbname VARCHAR(200), @prevdbname VARCHAR(200)

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

CREATE TABLE #sql_batch
(
	sql_text_row VARCHAR(MAX) NULL
)


CREATE TABLE #attach
(
	seq INT IDENTITY(1,1) PRIMARY KEY,
	dbname SYSNAME COLLATE database_default NULL,
	fileid INT NULL,
	filename VARCHAR(1000) COLLATE database_default NULL,
	txtattach VARCHAR(MAX) COLLATE database_default 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
							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)


-- per primo

-- abilita CLR
INSERT INTO #sql_batch VALUES
	('EXEC sp_configure ''show advanced options'', 1;')
INSERT INTO #sql_batch VALUES
	('RECONFIGURE;')


INSERT INTO #sql_batch VALUES
	('EXEC sp_configure ''clr enabled'', 1;')
INSERT INTO #sql_batch VALUES
	('RECONFIGURE;')


-- disabilita clr strict security
INSERT INTO #sql_batch VALUES
	('EXEC sp_configure ''clr strict security'', 0;')
INSERT INTO #sql_batch VALUES
	('RECONFIGURE;')


-- tutti i comandi di attach
INSERT INTO #sql_batch
	SELECT
			txtattach
		FROM
			(
				SELECT
						dbname
						, MAX(txtattach) AS txtattach
					FROM #attach
					GROUP BY
						dbname
			) AS x

INSERT INTO #sql_batch VALUES ('GO')

-- 
INSERT INTO #sql_batch 
	SELECT
			'ALTER DATABASE ' + name + ' SET RECOVERY FULL'
		FROM sys.sysdatabases
		WHERE
			name LIKE '%oltre%'
			OR name LIKE '%TouchFlow%'

INSERT INTO #sql_batch VALUES ('IF NOT EXISTS (SELECT * FROM syslogins WHERE name = ''Oltre3D'') CREATE LOGIN Oltre3D WITH PASSWORD=''Oltre3D'', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;') -- manca DEFAULT_DATABASE

INSERT INTO #sql_batch VALUES ('EXEC sp_addsrvrolemember N''Oltre3D'', N''dbcreator'';')

INSERT INTO #sql_batch VALUES ('GRANT VIEW SERVER STATE TO Oltre3D;')

INSERT INTO #sql_batch
	SELECT
			'USE ' + name + '; ' + CHAR(13) + CHAR(10)
			+ 'EXEC sp_changedbowner N''Oltre3D'''
		FROM sys.sysdatabases
		WHERE
			(
				name = 'us_misc'
				OR name = 'us_system'
				OR name LIKE '%oltre%'
				OR name = 'bancadati'
				OR name = 'quattroruote'
				OR name LIKE '%TouchFlow%'
			)

SELECT * FROM #sql_batch

DROP TABLE #attach
DROP TABLE #sql_batch
