USE [CN-GFC-PBD] -- ИМЯ ВАШЕЙ БД


select db_name(sa.dbid) as DBname 
	, sa.name as LogicalName,
	DATABASEPROPERTYEX(DB_NAME(),'Recovery') AS 'Database_RecoveryMode'
	, case sa.groupid
		WHEN 0 then 'LOG'
		ELSE sfg.groupname
	  end as Filegroup
	, sa.filename as Filename
	, cast(sf.size*8/1024. as numeric(19,0))as sizeMB
	, cast(sf.spaceused*8/1024. as numeric(19,0)) as spaceusedMB
	, cast((sf.size-sf.spaceused)*8/1024. as numeric(19,0)) as freespaceMB
	, case sf.maxsize
		when -1 then 'Unlimited'
		else cast(cast(sf.maxsize*8/1024.  as numeric(19,0))as varchar(22))
	  end as maxsizeMB
	, cast(sf.growth*8/1024. as numeric(19,0)) as nextgrowthMB
	--'USE [' + convert(nvarchar(MAX), db_name(sa.dbid)) + '];DBCC SHRINKFILE (N''' + convert (nvarchar(MAX), sa.name) + ''' ,' + convert (nvarchar (MAX), convert(INT, cast(sf.spaceused*8/1024. as numeric(19,0))) + CONVERT(INT, cast(sf.growth*8/1024. as numeric(19,0))) ) + ')' AS ShrinkCMD

from master..sysaltfiles sa 
left join (
	select   cast(size as bigint) as size
			,fileid
			,groupid
			,cast(fileproperty(name,'SpaceUsed')as bigint) as spaceused 
			,cast(maxsize as bigint) as maxsize
			,cast(case 
				when status & 0x100000 = 0 then growth
				else size*growth/100
			 end as bigint) as growth
	from sysfiles
	) sf on sf.fileid=sa.fileid 
		and sf.groupid = sa.groupid
left outer join sysfilegroups sfg on sfg.groupid = sf.groupid
where sa.dbid = db_id()

--- #################################
--AND
--(
--sa.name LIKE '%2016%'
--OR sa.groupid = 0 -- LOG FILES
--)
--- #################################

order by case when sa.groupid = 0 THEN 'Y' ELSE '-' end, sa.groupid, sa.fileid