SQLServer ~ Shrink all user databases

PS_SHRINK_ALL_USER_DATABASES.sql

USE master
GO

/*

EXEC PS_SHRINK_ALL_USER_DATABASES

*/

IF EXISTS(SELECT TOP 1 1 FROM sys.procedures WHERE name = 'PS_SHRINK_ALL_USER_DATABASES')
  DROP PROC PS_SHRINK_ALL_USER_DATABASES
GO

CREATE PROC PS_SHRINK_ALL_USER_DATABASES
AS
BEGIN

DECLARE @DBs TABLE(database_id NVARCHAR(max),name NVARCHAR(max))

INSERT @DBs 
SELECT database_id,name FROM sys.databases
WHERE name NOT IN ('master', 'model', 'tempdb', 'msdb', 'Resource')


DECLARE @DB_ID NVARCHAR(MAX) , 
		@DB_NAME NVARCHAR(MAX),
		@LOG_FILENAME NVARCHAR(MAX), 
		@DATA_FILENAME NVARCHAR(MAX)

WHILE EXISTS(SELECT TOP 1 1 FROM @DBs)
BEGIN
  SET @DB_ID = (SELECT TOP 1 database_id FROM @DBs)
  SET @DB_NAME = (SELECT TOP 1 name FROM @DBs)  
  SET @DATA_FILENAME = (SELECT TOP 1 name FROM sys.master_files WHERE database_id = @DB_ID   AND type = 0)
  SET @LOG_FILENAME = (SELECT TOP 1 name FROM sys.master_files WHERE database_id = @DB_ID   AND type = 1)

  EXEC('ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY SIMPLE')
  EXEC('USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @LOG_FILENAME + '], 1)')
  EXEC('USE [' + @DB_NAME + '] ; DBCC SHRINKFILE ([' + @DATA_FILENAME + '], 1)')
  EXEC('ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY FULL')

  DELETE @DBs WHERE database_id = @DB_ID
END



END

GO

EXEC PS_SHRINK_ALL_USER_DATABASES
GO