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.
Comentarios