Gloria Santin
Gloria Santin

Reputation: 2136

Table property 'name' in sys.filegroups is listed as 'Primary' when it is not needed

I am creating a script to create a table. When the table has a large object column such as varchar(max) the table needs the text TEXTIMAGE_ON [PRIMARY].

To determine the name of the file group, I use the system table, sys.filegroups and the column name. This returns the name of the file group. In the above example it is PRIMARY.

However, one of the tables that I am scripting has this value of PRIMARY when there are no columns in the table that are text, ntext, varchar(max), etc. So the script is failing when it gets to this table because TEXTIMAGE_ON is not allowed unless you have a large object column.

Why in the system table of sys.filegroups is the name not NULL because the TEXTIMAGE_ON should not be set?

This is the query I use to get the attribute of TextImageOnFileGroup:

SELECT [Table].name AS "TableName", fg.name AS "TextImageOnFileGroup"
FROM sys.tables as [Table] left outer join sys.filegroups as fg on [Table].lob_data_space_id = fg.data_space_id
WHERE [Table].name in ('ProviderSpecialty', 'ProviderState', 'ProviderExclusion') 

This returns

TableName            TextImageOnFileGroup
ProviderSpecialty         PRIMARY
ProviderState             NULL
ProviderAttribute         PRIMARY

The tables ProviderState does not have any columns that have a large object so TextImage_On is NULL. This is expected.

CREATE TABLE [dbo].[ProviderState](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProviderId] [numeric](19, 0) NOT NULL,
    [State] [nvarchar](2) NOT NULL,
 CONSTRAINT [PK_dbo.ProviderState] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The table ProviderAttribute has a varchar(max) column so the TextImage_on property should be set:

 CREATE TABLE [dbo].[ProductAttribute](
    [ProductAttributeId] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_dbo.ProductAttribute] PRIMARY KEY CLUSTERED 
(
    [ProductAttributeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The table ProviderSpecialty is the problem. It has the TextImage_on property set yet there is no column to warrant this.

CREATE TABLE [dbo].[ProviderSpecialty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProviderId] [numeric](19, 0) NOT NULL,
    [SpecialtyId] [int] NOT NULL,
 CONSTRAINT [PK_dbo.ProviderSpecialty] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I am using the system tables to create a script that creates these tables. If the property name in the sys.filegroups table is set to PRIMARY I add to the table script 'TEXTIMAGE_ON [PRIMARY]`.

When I run the script for this table, it errors because the TEXTIMAGE_ON property is not allowed unless there is a column of text, varchar(max), etc.

Why is the property set in the system table if it is not needed?

Upvotes: 0

Views: 214

Answers (0)

Related Questions