How to search partition schema, filegroup and text image at once in SQL Server?-CodePudding

The following query was performed.

CREATE TABLE [dbo].[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

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 comment', 
    @level0type=N'SCHEMA', @level0name=N'dbo', 
    @level1type=N'TABLE', @level1name=N'TB_FILEGROUP'
GO

I want to search the partition schema, filegroup, text image, and table comments all at once.

Please advise.

Detailed Question

A partition schema (partition column) is specified for table A.
A filegroup is assigned to table B.
A text image is assigned to table C.

And I want to lookup tables A, B, and C all at once for schema. (Each table is commented.)

As a result of my testing, if table B with a filegroup specified is searched, table A cannot be searched due to conditions.

Also, text images cannot be viewed.

Obviously I'm looking it up wrong, but I need help with this.

CodePudding user response：

You can use the catalog view sys.extended_properties

select *
from sys.extended_properties ep
where ep.name = 'MS_Description';

db<>fiddle