none
请问 只存有分区表的文件组是不是用下面的命令查不到 任何相关的对像信息? RRS feed

  • 问题

  • 请问  只存有分区表的文件组是不是用下面的命令查不到 任何相关的对像信息?

    SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
    FROM sys.indexes i
    INNER JOIN sys.filegroups f
    ON i.data_space_id = f.data_space_id
    INNER JOIN sys.all_objects o
    ON i.[object_id] = o.[object_id]
    WHERE f.name = 'fqDATA201303'
    GO 

    事实上是有表在其中的。



        select sp.destination_id, sp.partition_scheme_id, fg.data_space_id,fg.name 
        from sys.destination_data_spaces sp
        inner join sys.partition_schemes scm on sp.partition_scheme_id = scm.data_space_id
        inner join sys.filegroups fg on fg.data_space_id = sp.data_space_id
        where scm.name='fqfa_pPOD' and sp.destination_id in 
        (1,2,3,4,5,6,7,8,9,10,11,12,13)  ;
        
        
       destination_id partition_scheme_id data_space_id name
    1 65601 9 fqDATA201303
    2 65601 15 fqDATA201309
    3 65601 16 fqDATA201310
    4 65601 17 fqDATA201311
    5 65601 18 fqDATA201312
    6 65601 19 fqDATA201401
    7 65601 20 fqDATA201402
    8 65601 21 fqDATA201403
    9 65601 22 fqDATA201404
    10 65601 23 fqDATA201405
    11 65601 24 fqDATA201406
    12 65601 25 fqDATA201407
    13 65601 26 fqDATA201408

    2014年8月9日 9:33

答案

  • Following lists all objects in specific file group:

    DECLARE @FGID INT; 
    SET @FGID = 1; -- Replace Filegroup ID Here   
    SELECT QUOTENAME(s.name) AS SchemaName, QUOTENAME(o.name) AS ObjName, o.object_id AS ObjID, p.index_id AS IndexID,    
    QUOTENAME(i.name) AS IndexName, ROUND(CAST(au.data_pages AS FLOAT)/128,2) AS MB_Used, QUOTENAME(f.name) AS DataFilegroup,    
    QUOTENAME(f2.name) AS LOBFilegroup FROM sys.allocation_units au     
    INNER JOIN sys.partitions p ON au.container_id = p.partition_id     
    INNER JOIN sys.objects o ON p.object_id = o.object_id     
    INNER JOIN sys.indexes i ON p.index_id = i.index_id AND i.object_id = p.object_id     
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id     
    LEFT JOIN sys.tables t ON o.object_id = t.object_id     
    LEFT JOIN sys.filegroups f ON au.data_space_id = f.data_space_id     
    LEFT JOIN sys.filegroups f2 ON t.lob_data_space_id = f2.data_space_id 
    WHERE au.[type] = 1 AND (au.data_space_id = @FGID OR t.lob_data_space_id = @FGID) 
    ORDER BY SchemaName, ObjName, p.index_id;

    2014年8月11日 13:17

  • SELECT  CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme ,
            p.partition_number ,
            CONVERT(VARCHAR(MAX), ds2.name) AS filegroup ,
            CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary ,
            STR(p.rows, 9) AS rows
    FROM    sys.indexes i
            JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
            JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
            JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
            JOIN sys.partitions p ON dds.destination_id = p.partition_number
                                     AND p.object_id = i.object_id
                                     AND p.index_id = i.index_id
            JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
            LEFT JOIN sys.Partition_Range_values v ON pf.function_id = v.function_id
                                                      AND v.boundary_id = p.partition_number
                                                      - pf.boundary_value_on_right
    WHERE   i.object_id = OBJECT_ID('表名')
            AND i.index_id IN ( 0, 1 )
    ORDER BY p.partition_number



    2014年8月15日 14:42

全部回复

  • SELECT p.partition_number, fg.name, p.rows
    FROM sys.partitions p  INNER JOIN sys.allocation_units au  ON au.container_id = p.hobt_id 
    INNER JOIN sys.filegroups fg  ON fg.data_space_id = au.data_space_id WHERE p.object_id = OBJECT_ID(@TableName)

    用上面的代码可以查到吗?


    Please Mark As Answer if it is helpful.

    2014年8月9日 11:27
  • 根据联机帮助上的说明

    http://msdn.microsoft.com/zh-cn/library/ms173760.aspx

    <sentencetext xmlns="http://www.w3.org/1999/xhtml">data_space_id</sentencetext>                                                              

    int                                       

    <sentencetext xmlns="http://www.w3.org/1999/xhtml">该索引的数据空间的 ID。</sentencetext>                     <sentencetext xmlns="http://www.w3.org/1999/xhtml">数据空间是文件组或分区方案。</sentencetext>

    object_id 是表值函数或内存中索引。</sentencetext>

    对于分区表, data_space_id 对应的应该是分区方案的 space_id, 所以直接 JOIN filegroups 应该是出不来结果

    2014年8月11日 1:54
  • 你可以用 sys.data_spaces 代替sys.filegroups 做查询, 如果你需要每个 partition 对应的明细信息,可以用2楼的
    2014年8月11日 2:21
  • Following lists all objects in specific file group:

    DECLARE @FGID INT; 
    SET @FGID = 1; -- Replace Filegroup ID Here   
    SELECT QUOTENAME(s.name) AS SchemaName, QUOTENAME(o.name) AS ObjName, o.object_id AS ObjID, p.index_id AS IndexID,    
    QUOTENAME(i.name) AS IndexName, ROUND(CAST(au.data_pages AS FLOAT)/128,2) AS MB_Used, QUOTENAME(f.name) AS DataFilegroup,    
    QUOTENAME(f2.name) AS LOBFilegroup FROM sys.allocation_units au     
    INNER JOIN sys.partitions p ON au.container_id = p.partition_id     
    INNER JOIN sys.objects o ON p.object_id = o.object_id     
    INNER JOIN sys.indexes i ON p.index_id = i.index_id AND i.object_id = p.object_id     
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id     
    LEFT JOIN sys.tables t ON o.object_id = t.object_id     
    LEFT JOIN sys.filegroups f ON au.data_space_id = f.data_space_id     
    LEFT JOIN sys.filegroups f2 ON t.lob_data_space_id = f2.data_space_id 
    WHERE au.[type] = 1 AND (au.data_space_id = @FGID OR t.lob_data_space_id = @FGID) 
    ORDER BY SchemaName, ObjName, p.index_id;

    2014年8月11日 13:17
  • 结果是这样

    partition_number name rows
    2 fqDATA201309 5585243
    2 fqDATA201309 5585243
    2 fqDATA201309 5585243
    2 fqDATA201309 5585243
    3 fqDATA201310 6094572
    3 fqDATA201310 6094572
    3 fqDATA201310 6094572
    3 fqDATA201310 6094572
    4 fqDATA201311 7463231
    4 fqDATA201311 7463231
    4 fqDATA201311 7463231
    4 fqDATA201311 7463231
    5 fqDATA201312 7158889
    5 fqDATA201312 7158889
    5 fqDATA201312 7158889
    5 fqDATA201312 7158889
    6 fqDATA201401 4366460
    6 fqDATA201401 4366460
    6 fqDATA201401 4366460
    6 fqDATA201401 4366460
    7 fqDATA201402 3465665
    7 fqDATA201402 3465665
    7 fqDATA201402 3465665
    7 fqDATA201402 3465665
    8 fqDATA201403 6467343
    8 fqDATA201403 6467343
    8 fqDATA201403 6467343
    8 fqDATA201403 6467343
    9 fqDATA201404 6489416
    9 fqDATA201404 6489416
    9 fqDATA201404 6489416
    9 fqDATA201404 6489416
    10 fqDATA201405 6401364
    10 fqDATA201405 6401364
    10 fqDATA201405 6401364
    10 fqDATA201405 6401364
    11 fqDATA201406 5775947
    11 fqDATA201406 5775947
    11 fqDATA201406 5775947
    11 fqDATA201406 5775947
    12 fqDATA201407 5321598
    12 fqDATA201407 5321598
    12 fqDATA201407 5321598
    12 fqDATA201407 5321598
    13 fqDATA201408 2515276
    13 fqDATA201408 2515276
    13 fqDATA201408 2515276
    13 fqDATA201408 2515276
    1 fqDATA201303 9210374
    1 fqDATA201303 9210374
    1 fqDATA201303 9210374
    1 fqDATA201303 9210374

    2014年8月15日 7:54
  • 非常感谢 !!!!!!!!

    这位兄弟你给我这个要出来的结果太清晰了。

    现在有一个新的疑问是,出来的结果中只包含了索引, 没有体现出行数据吗。 因为这个分区 对应的文件有41G左右, 实际使用也有25389MB。而现在统计出来的像是只有索引信息 总讲是: 15322MB    怎么样才能了体现出 行数据?  麻请再次赐教。

    IndexName MB_Used DataFilegroup
    [IX_aInvdetFee_FeeDateTime] 4308.5 [fqDATA201303]
    [PK_aInvdetFee] 345.91 [fqDATA201303]
    [IX_aInvdetFee_Jobno] 300.23 [fqDATA201303]
    [IX_aInvdetFee_AFDateTime] 246.79 [fqDATA201303]
    [IX_aInvdetFee_DFDateTime] 255.11 [fqDATA201303]
    [IX_aInvoiceFeeBalanceDetail_BalanceDateTime] 1554.45 [fqDATA201303]
    [IX_aInvoiceFeeBalanceDetail_FeeDateTime] 294.31 [fqDATA201303]
    [IX_aInvoiceFeeBalanceDetail_Jobno] 341.02 [fqDATA201303]
    [IX_aInvoiceFeeBalanceDetail_ID] 267.62 [fqDATA201303]
    [IX_mManifest_SDDate] 3863.49 [fqDATA201303]
    [PK_mManifest_Jobno] 259.12 [fqDATA201303]
    [IX_pPod_ScanDateTime] 2126.7 [fqDATA201303]
    [IX_pPod_Jobno] 439.79 [fqDATA201303]
    [IX_pPod_ID] 341.37 [fqDATA201303]
    [IX_pPod_CreateDateTime] 377.82 [fqDATA201303]

    2014年8月15日 8:51

  • SELECT  CONVERT(VARCHAR(MAX), ps.name) AS partition_scheme ,
            p.partition_number ,
            CONVERT(VARCHAR(MAX), ds2.name) AS filegroup ,
            CONVERT(VARCHAR(MAX), ISNULL(v.value, ''), 120) AS range_boundary ,
            STR(p.rows, 9) AS rows
    FROM    sys.indexes i
            JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
            JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
            JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
            JOIN sys.partitions p ON dds.destination_id = p.partition_number
                                     AND p.object_id = i.object_id
                                     AND p.index_id = i.index_id
            JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
            LEFT JOIN sys.Partition_Range_values v ON pf.function_id = v.function_id
                                                      AND v.boundary_id = p.partition_number
                                                      - pf.boundary_value_on_right
    WHERE   i.object_id = OBJECT_ID('表名')
            AND i.index_id IN ( 0, 1 )
    ORDER BY p.partition_number



    2014年8月15日 14:42
  • Did you create tables on same partition schema?
    2014年8月15日 19:59