CODEWITHSUNDEEP
sql-serversql-server-2008ssms
rene
rene

Reputation: 42414

SQL Server Management Studio description on foreignkey weirdness

In SQL Server Management Studio we enter a description for a foreign key in this dialog (modify on foreign key from table Campus):

SQL Server Management Studio dialog

As you can see, the description seems to be emtpy.

However, if I run this query:

select object_name([major_id]), [ep].[name], [value]
from sys.extended_properties ep
INNER join sys.objects o on o.object_id = major_id
where o.[type] = 'F'

the result is:

FK_Campus_School    MS_Description  foobar

If I enter a text in the description field (because I think the field is not yet set) I overwrite the original value.

I know I can use this stored procedure to add/update extended properties from a script:

EXEC sys.sp_addextendedproperty /* sp_updateextendedproperty */
@name = N'MS_Description', 
@value = N'is this saved again?', 
@level0type = N'SCHEMA', 
@level0name = N'Data', 
@level1type = N'TABLE', 
@level1name = N'Campus',
@level2type = N'CONSTRAINT', 
@level2name = N'FK_Campus_School';

Notice that calling sp_addextendedproperty does raise an exception if the property is already defined. The dialog seems to always do an update.

What have I tried

The text is shown if I open the foreign key dialog on the referenced table. That dialog show the FK_Campus_School key as well, and after selecting that one, the description is shown. Going back to the Campus table the foreign key description is then shown. However, after executing a single query I'm in the same boat again.

Hitting the Refresh button and/or context menu option didn't resolve this.

Is there anything else I can do to have SQL Server Management Studio show the description reliably in the foreign key modify dialog?

Version information:

| Microsoft SQL Server Management Studio  | 10.50.1617.0
| Microsoft Data Access Components (MDAC) | 6.1.7601.17514
| Microsoft MSXML                         | 3.0 4.0 5.0 6.0 
| Microsoft Internet ExploreR             | 8.0.7601.17514
| Microsoft .NET Framework                | 2.0.50727.5472
| Operating SysteM                        | 6.1.7601

Target: SQL Server 2008 R2

Upvotes: 4

Views: 654

Answers (1)

mehdi lotfi
mehdi lotfi

Reputation: 11571

You must refresh your IntelliSense:

  • In SQL Server Management Studio, menu Edit -> IntelliSense -> Refresh Local Cache

OR

  • Ctrl + Shift + R

It's working in SQL Server 2012. I also tested my solution in SQL Server 2008 R2, but it does not work.

Upvotes: 2

Related Questions