SP to Automatically Shrink all Files on all DBs on a Server

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: K Griffith
-- Create date: 28 Dec 2010
-- Description: Dynamically loops through all the files of a given DB and shrinks them.
-- Caveat: This procedure had to be done with Dynamic SQL because functions
-- (such as USE) cannot have variables passed to them. Additionally, EXEC()
-- starts a new thread and is unaware of anything going on around it.
-- Therefore, the ENTIRE procedure had to be coded using Dynamic SQL and
-- EXEC().
-- =============================================
ALTER PROCEDURE [dbo].[sp_ShrinkFiles]
-- Add the parameters for the stored procedure here
@DB_NAME NVARCHAR(300)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL AS NVARCHAR(500)
DECLARE @SQL1 AS NVARCHAR(500)
DECLARE @SQL2 AS NVARCHAR(500)
DECLARE @SQL3 AS NVARCHAR(500)
DECLARE @SQL4 AS NVARCHAR(500)

-- Select the DB for use
SET @SQL = 'USE [' + @DB_NAME + ']'

-- Declare and set variables dynamically to run in the EXEC() function
SET @SQL1 = ' DECLARE @FILES AS INT
DECLARE @FILENAME AS VARCHAR(500)
DECLARE @SPACEUSED AS INT
SET @FILES = (SELECT COUNT(NAME) FROM sys.database_files)'

-- Shrink the files by setting the recovery model to SIMPLE
SET @SQL2 = ' ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY SIMPLE '

-- Loop through all the files in the given DB. Get file names and Space Used by each
-- Shrink each file
SET @SQL3 = ' WHILE @FILES > 0
BEGIN
SET @FILENAME = (SELECT name FROM sys.database_files
WHERE FILE_ID = @FILES)

SET @SPACEUSED = (SELECT CAST(FILEPROPERTY(name, ''SpaceUsed'')
AS int)/128.0
FROM sys.database_files
WHERE FILE_ID = @FILES)

DBCC SHRINKFILE (@FILENAME, @SPACEUSED)

SET @FILES = @FILES - 1
END '

-- Reset the database recovery model.
SET @SQL4 = ' ALTER DATABASE [' + @DB_NAME + '] SET RECOVERY FULL'

-- Execute all of the Dynamic SQL
EXEC (@SQL + @SQL1 + @SQL2 + @SQL3 + @SQL4)

END