1

Closed

Missing Extended Property for Primary Key Indexes on tables

description

When scripting extended properties, the extended property for the primary key INDEX is not included.
 
EXEC sp_addextendedproperty N'MS_Description', N'Clustered index created by a primary key constraint.', 'SCHEMA', N'dbo', 'TABLE', N'AWBuildVersion', 'INDEX', N'PK_AWBuildVersion_SystemInformationID'
or
EXEC sp_addextendedproperty N'MS_Description', N'Nonclustered index created by a primary key constraint.', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'INDEX', N'PK_BillOfMaterials_BillOfMaterialsID'
 
 
While the extended property for the primary key CONSTRAINT is included.
 
EXEC sp_addextendedproperty N'MS_Description', N'Primary key (clustered) constraint', 'SCHEMA', N'dbo', 'TABLE', N'AWBuildVersion', 'CONSTRAINT', N'PK_AWBuildVersion_SystemInformationID'
or
EXEC sp_addextendedproperty N'MS_Description', N'Primary key (clustered) constraint', 'SCHEMA', N'Production', 'TABLE', N'BillOfMaterials', 'CONSTRAINT', N'PK_BillOfMaterials_BillOfMaterialsID'
 
NB: The text description for PK_BillOfMaterials_BillOfMaterialsID, 'Primary key (clustered) constraint' in the given example is incorrect. The clustered index for this table was something other than the primary key, and extended properties for it were scripted successfully. The PK in that example was non-clustered (yet still not scripted). This points towards the issue being related to Primary Keys and not Clustered Indexes.
Closed Aug 26, 2007 at 11:30 PM by ivanjh
Issue resolved & fix published.

comments

ivanjh wrote Aug 22, 2007 at 7:19 AM

Example:CREATE TABLE [dbo].[TestTable]( [TestCol1] [int] NOT NULL, CONSTRAINT [PK_TestTable] PRIMARY KEY NONCLUSTERED ([TestCol1] ASC))

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TestTable CONSTRAINT' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TestTable', @level2type=N'CONSTRAINT',@level2name=N'PK_TestTable'EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'TestTable INDEX' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TestTable', @level2type=N'INDEX',@level2name=N'PK_TestTable'SELECT objtype, objname, name, value FROM fn_listextendedproperty(default, 'SCHEMA','dbo', 'TABLE', 'TestTable', 'CONSTRAINT', 'PK_TestTable')SELECT objtype, objname, name, value FROM fn_listextendedproperty(default, 'SCHEMA','dbo', 'TABLE', 'TestTable', 'INDEX', 'PK_TestTable')

You'll see two different properties for PK_TestTable. One as an index, the another as a constraint.SSMS 2005 will only script out the constraint portion.Extended properties on the PK via SMO accesses the CONSTRAINT level type.

ivanjh wrote Aug 23, 2007 at 4:46 AM

Table scripting method extended to check for extended properties on primary key indexes.Fixed after 20070822beta.

wrote Aug 23, 2007 at 4:46 AM

wrote Aug 26, 2007 at 11:30 PM

wrote Mar 16, 2011 at 5:09 AM

wrote Feb 14, 2013 at 1:49 AM

wrote May 16, 2013 at 5:53 AM