check partition table information for partition table in MSSQL on special disk

Yes, sometimes, We needs to check out which server disk have partition table part datafiles

and partition table information.

SQL:

with TAB1 as (

select b.name as "filegroupname",a.name as "datafilename",b.physical_name,b.size/128 as "sizeMB",b.growth/128 as "GrowthGB"

-- a.name,b.name,b.physical_name,size/128,growth/128

from sys.filegroups a inner join sys.database_files b

on a.data_space_id=b.data_space_id

where LEFT(physical_name,1)='I'

)

select distinct c.TABLE_NAME,TAB1.filegroupname,TAB1.datafilename,TAB1.physical_name,TAB1.sizeMB,TAB1.GrowthGB from partition_show c inner join TAB1 on c.File_group_name=TAB1.datafilename

Yes, You could use that SQL to check it.

That sql have two parts , one is CTE ,another is a view for checking partition table, you could see it in my blog.

That SQL means to check and match two parts rows , which means find out common rows between two tables or views.