Extended Properties Code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Extended Properties Code

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-21 : 10:55:39

Good morning, I have this little slice of code that I am trying to get to work. If the extended property already exists this code throws an error, I thought it was going to just update the property?

USE NCOS
GO

if exists (select * from ::fn_listextendedproperty ('MS_Description','SCHEMA','NCOS','TABLE','NCOS_Data','COLUMN','NC_LeadDate'))
begin
EXEC sp_updateextendedproperty
'MS_Description',
'Fred',
'SCHEMA',
'dbo',
'table',
'NCOS_Data',
'COLUMN',
'NC_LeadDate'
end
else
begin
EXEC sp_addextendedproperty
'MS_Description',
'Lead Date',
'SCHEMA',
'dbo',
'table',
'NCOS_Data',
'COLUMN',
'NC_LeadDate'
end

Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37
Property cannot be added. Property 'MS_Description' already exists for 'dbo.NCOS_Data.NC_LeadDate'.

Thanks in advance as always.

Bryan Holmstrom

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-21 : 16:55:57

-- Try this... no idea if it will make any difference though
if exists (select * from fn_listextendedproperty (default, default, default, default, default, default, default) WHERE Name = 'MS_Description')

OR

Should 'NCOS' not be 'dbo'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-22 : 00:58:23

the schemas are different in checking and updation part. the if check part looks for NCOS_Data table in NCOS schema but you're trying to update extendedproperty of NCOS_Data table in dbo schema

try this


if exists (select * from ::fn_listextendedproperty ('MS_Description','SCHEMA','dbo','TABLE','NCOS_Data','COLUMN','NC_LeadDate'))
begin
EXEC sp_updateextendedproperty 
'MS_Description', 
'Fred', 
'SCHEMA',
'dbo', 
'table',
'NCOS_Data',
'COLUMN',
'NC_LeadDate'
end
else
begin
EXEC sp_addextendedproperty 
'MS_Description', 
'Lead Date', 
'SCHEMA',
'dbo', 
'table',
'NCOS_Data',
'COLUMN',
'NC_LeadDate'
end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources