TSQL – Listing Filegroups linked Objects

database_filegroup_objects_get_TSQL

-- =================================================
-- Best viewing if using tabs = 2 to your editor
-- =================================================
-- Author				:	Thierry Bertin
-- 							: www.abcdata.ca
-- Create date	: 2018-03-29
-- Description	: List Filegroups Objects 
-- =================================================
declare @FileGroup_Name																nvarchar	(250);

set @FileGroup_Name = N'%';

select					a.name														as	Name_of_Filegroup	
							, object_name (b.object_id)					as	Name_of_Object
							,	b.name														as	Index_Name
							,	b.type_desc												as	Index_Type
							,	c.type														as	Object_Type
							,	c.type_desc												as	Object_Description
from						sys.filegroups as a 
								left	outer	join	sys.indexes as b 
								on								a.data_space_id = b.data_space_id
																	inner	join	sys.all_objects as c 
																	on					c.object_id = b.object_id
where						lower (a.name) like @FileGroup_Name				
order by				5 , 1 , 2;