查询分区情况多表

t1292582463

-------多个表用逗号拼接表名
DECLARE @tabname NVARCHAR(MAX) = '表1,表2,表3'

SELECT tv.value AS '表名',t.name AS '分区索引',tc.name AS 分区字段,t1.name AS '分区方案',t2.name AS '分区函数',t3.value AS '分区界限',t5.name AS '文件组',t6.name AS '文件',t6.physical_name AS '文件位置'
FROM sys.indexes t
INNER JOIN (SELECT OBJECT_ID(value) AS objectid,value FROM dbo.Split(@tabname,',')) tv ON tv.objectid = t.object_id
OUTER APPLY(SELECT ISNULL((SELECT STUFF((SELECT ','+ name FROM (SELECT CAST(name AS NVARCHAR(256)) AS name FROM sys.columns a  INNER JOIN sys.index_columns tci ON a.column_id = tci.column_id AND a.object_id = tci.object_id
WHERE tci.index_id = t.index_id AND tci.object_id = t.object_id )ty FOR XML PATH('')),1,1,'')),'') AS name) tc
LEFT JOIN sys.partition_schemes t1 ON t1.data_space_id = t.data_space_id
LEFT JOIN sys.partition_functions t2 ON t2.function_id = t1.function_id
LEFT JOIN sys.partition_range_values t3 ON t3.function_id = t1.function_id
LEFT JOIN sys.destination_data_spaces t4 ON t4.partition_scheme_id = t1.data_space_id AND t4.destination_id = t3.boundary_id
LEFT JOIN SYS.filegroups t5 ON t5.data_space_id = t4.data_space_id
LEFT JOIN SYS.database_files t6 ON t6.data_space_id = t4.data_space_id
WHERE t.data_space_id > 1