How to inquire partition schema, filegroup, etc. in sqlserver?

user_13746660 0
Viewed 30

This is a similar question to the one I posted earlier, but with changes.

I created a table like this:

Table with partition schema applied

CREATE TABLE TB_PARTITION_SCHEMA (
    COL INT,
    COL2 INT
)
    ON PartitionSchema (COL)
GO;

EXEC sp_addextendedproperty 
    @name=N'MS_Description', @value=N'Partition Schema Comment', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'TB_PARTITION_SCHEMA'
GO

Table with filegroup applied

CREATE TABLE [dbo].[TB_ONLYFG] (
    [COL] [INT]
)
    ON [test2fg]
GO

EXEC sp_addextendedproperty 
    @name=N'MS_Description', @value=N'Only FileGroup', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'TB_ONLYFG'
GO

Table with text image applied

CREATE TABLE [dbo].[TB_TEXTIMAGE] (
    [COL] [VARCHAR](max)
)
    TEXTIMAGE_ON [test1fg]
GO

EXEC sp_addextendedproperty 
    @name=N'MS_Description', @value=N'Only TextImage', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'TB_TEXTIMAGE'
GO

Table with filegroup and text image applied

CREATE TABLE [dbo].[TB_FILEGROUP] (
    [COL] [INT],
    [COL2] [VARCHAR](max) 
)
    ON [test1fg]
    TEXTIMAGE_ON [test2fg]
GO

EXEC sp_addextendedproperty 
    @name=N'MS_Description', @value=N'fileGroup, TextImage Comment', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'TB_FILEGROUP'
GO

I want to search these tables at once. The query I've tried is:

SELECT
        t.name as tableName
    ,   f.name as fileGroupName
    ,   f.log_filegroup_id 
    ,   t.lob_data_space_id 
    ,   CAST(ep.value AS NVARCHAR(4000)) AS comment
FROM sys.indexes as i
INNER JOIN sys.tables as t ON t.object_id = i.object_id
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
LEFT JOIN sys.extended_properties ep ON ep.class = 1
AND ep.minor_id = 0
AND t.object_id = ep.major_id
AND ep.name = N'MS_Description'
WHERE t.schema_id = 1
ORDER BY t.name ASC
tableName fileGroupName logFileGroupId lob_data_space_id comment
TB_FILEGROUP test1fg NULL 3 fileGroup, TextImage Comment
TB_ONLYFG test2fg NULL 0 Only FileGroup
TB_TEXTIMAGE PRIMARY NULL 2 Only TextImage

The question is:

  1. There is no way to query the partition schema and partition columns in the current query. What is it?

  2. How to search data specified as text image?

For both questions, I want the partition schema for each table and the partition column to be verifiable as a column as shown in the table above.

sql-server
1 Answers

The problem is that although partition schemes are data spaces, they are not a filegroup, so you won't find them in sys.filegroups. Instead you need to join sys.data_spaces.

You should also filter sys.indexes to only the heap or clustered index, otherwise you could get multiple rows per table.

It's unclear what you mean by fileGroup in the comments column, I presume you mean to check whether it's the default filegroup.

lob_data_space_id on the sys.tables view will tell you if TEXT_IMAGE is being used.

The extended properties can be aggregated in a subquery using STRING_AGG.

select
  tableName = t.name,
  d.type_desc space_type,
  fileGroupName = d.name,
  t.lob_data_space_id,
  comment = CONCAT_WS(',
',
      CASE WHEN d.is_default = 0 THEN d.type_desc END,
      CASE WHEN t.lob_data_space_id > 0 THEN 'TEXT_IMAGE' END,
      ep.props
    )
from sys.tables t
join sys.indexes i on i.object_id = t.object_id and i.index_id in (0,1)
join sys.data_spaces d on d.data_space_id = i.data_space_id
left join sys.data_spaces td on td.data_space_id = t.lob_data_space_id
outer apply (
    select string_agg(cast(ep.value as nvarchar(4000)), '
')
    from sys.extended_properties ep
    where ep.major_id = t.object_id
      and ep.name = 'MS_Description'
) ep(props);

db<>fiddle

user_148689970
Related