SET NOCOUNT ON

DECLARE @dbName nvarchar(100)
DECLARE @sqlstmt nvarchar(4000)
DECLARE @dbContext nvarchar(256)
DECLARE @rowcnt INT
DECLARE @iterator INT,
@sortby varchar(20) = 'drive'

DECLARE DBcursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc <> 'OFFLINE'

CREATE TABLE #db_file_info (
[Drive Letter] CHAR(01) NULL,
[Database Name] SYSNAME NULL,
[File Type] VARCHAR(10) NULL,
[Logical File Name] SYSNAME NULL,
[Physical Name] VARCHAR(500) NULL,
[File Size (MB)] FLOAT NULL,
[File Size (GB)] FLOAT NULL,
[Space Used (MB)] INT NULL,
[Space Used (GB)] FLOAT NULL,
[Space Left (MB)] FLOAT NULL,
[Space Left (GB)] FLOAT NULL,
[Percent Free Space] FLOAT NULL,
[Max File Size (MB)] FLOAT NULL,
[Max File Size (GB)] FLOAT NULL,
[Autogrowth (MB)] FLOAT NULL,
[File ID] int NULL,
[DB State] VARCHAR(10) NULL,
[Shrink File (Truncate free space)] VARCHAR(8000) NULL,
[Shrink file To 20percent freespace] VARCHAR(8000) NULL,
[Increase File Size by 20percent] VARCHAR(8000) NULL
)

OPEN DBcursor
FETCH DBcursor INTO @dbName

WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @dbContext = '[' + @dbName + ']' + '.dbo.' + 'sp_executesql'
SET @sqlstmt = ' Insert into #db_file_info
SELECT LEFT(physical_name,1) AS drive_letter
, db_name()
, CASE
WHEN RIGHT(physical_name,4) = ''.mdf'' THEN ''mdf''
WHEN RIGHT(physical_name,4) = ''.ndf'' THEN ''ndf''
WHEN RIGHT(physical_name,4) = ''.ldf'' THEN ''ldf''
ELSE type_desc
END as ''File Type''
, name
, physical_name
, ROUND( (((CAST([size] as FLOAT) * 8192) /1024) /1024), 2) as [File Size MB]
, ROUND( ((((CAST([size] as FLOAT) * 8192) /1024) /1024) /1024), 2) as [File Size GB]
, ROUND( (((CAST(fileproperty(name,''SpaceUsed'') as FLOAT) * 8192) /1024) /1024), 2) as [Space Used MB]
, ROUND( ((((CAST(fileproperty(name,''SpaceUsed'') as FLOAT) * 8192) /1024) /1024) /1024), 2) as [Space Used GB]
, ROUND( (((CAST((size-fileproperty(name,''SpaceUsed'')) as FLOAT) * 8192) /1024) /1024), 2) as [Space Left MB]
, ROUND( ((((CAST((size-fileproperty(name,''SpaceUsed'')) as FLOAT) * 8192) /1024) /1024) /1024), 2) as [Space Left GB]
, ROUND(
ROUND( (((CAST((size-fileproperty(name,''SpaceUsed'')) as FLOAT) * 8192) /1024) /1024), 2) /
ROUND( (((CAST([size] as FLOAT) * 8192) /1024) /1024), 2)
* 100
, 2) as [percent free space]

, CASE [max_size]
WHEN -1 THEN 0
ELSE ROUND( (((CAST([max_size] as FLOAT) * 8192) /1024) /1024), 2)
END as [Max File Size (MB)]
, CASE [max_size]
WHEN -1 THEN 0
ELSE ROUND( ((((CAST([max_size] as FLOAT) * 8192) /1024) /1024) /1024), 2)
END as [Max File Size (GB)]
, ROUND( (((CAST(growth as FLOAT) * 8192) /1024) /1024), 2) as [Autogrowth (MB)]
, file_id
, state_desc as [DB State]
, ''USE ['' + DB_NAME() + '']; DBCC SHRINKFILE (N'''''' + name + '''''' , 0, TRUNCATEONLY);'' as [Shrink_FileSize_command]
, ''USE ['' + DB_NAME() + '']; DBCC SHRINKFILE (N'''''' + name + '''''' , ''
+ CAST( ROUND ( 1.2*(ROUND( (((CAST(fileproperty(name,''SpaceUsed'') as FLOAT) * 8192) /1024) /1024), 2)),0) AS VARCHAR(20))
+ '');'' as [Shrink_FileSize_command]
, ''USE [MASTER]; ALTER DATABASE ['' + DB_NAME() + ''] MODIFY FILE (NAME = '' + name + '', SIZE = ''
+ CAST( ROUND( 1.2*(ROUND( (((CAST(fileproperty(name,''SpaceUsed'') as FLOAT) * 8192) /1024) /1024), 2)), 0) AS VARCHAR(20))
+ ''MB)'' as [Increase_FileSize_by20]

FROM sys.database_files

'

--PRINT @sqlstmt
EXEC @dbContext @sqlstmt
FETCH DBcursor INTO @dbName
END

CLOSE DBcursor
DEALLOCATE DBcursor

SET @sqlstmt = 'SELECT * FROM #db_file_info '
IF @sortby = 'drive'
SET @sqlstmt = @sqlstmt + 'ORDER BY [Drive Letter], [Database Name]'
ELSE IF @sortby = 'db'
SET @sqlstmt = @sqlstmt + 'ORDER BY [Database Name], [Percent Free Space]'
ELSE IF @sortby = 'freespace'
SET @sqlstmt = @sqlstmt + 'ORDER BY [Percent Free Space], [Database Name]'

EXEC (@sqlstmt)

DROP TABLE #db_file_info