Shrink Data Files to Appropriate Target Size

If you need to shrink data files for any reason, despite the potential negative impact on future performance due to file growth and fragmentation, this script can be used to automatically shrink them without first calculating an appropriate target size.

DECLARE @sqlcommand nvarchar(max) = N'';

SELECT @sqlcommand +=
    N'DBCC SHRINKFILE (N''' + s.name + ''', ' +
    CAST(
        ROUND(
            CAST(
                CASE s.type
                    WHEN 2 THEN s.size * 8.0
                    ELSE dfs.allocated_extent_page_count * 8.0
                END
            AS float) / 1024,
            -1
        ) AS varchar(20)
    ) + N');' + CHAR(13) + CHAR(10)
FROM sys.database_files s
JOIN sys.filegroups g
    ON s.data_space_id = g.data_space_id
LEFT JOIN sys.dm_db_file_space_usage dfs
    ON dfs.database_id = DB_ID()
   AND dfs.file_id = s.file_id
WHERE s.type_desc = 'ROWS'
  AND s.drop_lsn IS NULL;

PRINT @sqlcommand;
EXEC sp_executesql @sqlcommand;