extended property mass update using temporary table

Question

I need to semi-automate an extended property update and what I planned to do, rightly or wrongly, is to populate a temporary table with all of the data, then somehow join to the temporary table when checking to see if the value already exists. Then I want to iterate through each row in the temporary table and if the value exists, call the function sp_updateextendedproperty, and if it doesn't call the function sp_addextendedproperty. Below is my starting point, what would make this work?

DECLARE @Table TABLE (id int IDENTITY(1,1),TableName SYSNAME, ColumnName varchar(200), ColumnDescription varchar(200))

INSERT INTO @Table VALUES('MyTable', 'Col1', 'Col1 description')
INSERT INTO @Table VALUES('MyTable', 'Col2', 'Col2 description')

IF EXISTS 
(select 
    sc.name,
    sep.value
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
                                 and sc.column_id = sep.minor_id
                                 and sep.name = 'MS_Description'
where st.name = @Table.TableName and sc.name = @Table.ColumnName and sep.value is not null)

EXEC sp_updateextendedproperty 
@name = N'MS_Description', @value = @Table.ColumnDescription,
@level0type = N'Schema', @level0name = 'dbo', 
@level1type = N'Table',  @level1name = @Table.TableName, 
@level2type = N'Column', @level2name = @Table.ColumnName

ELSE

EXEC sp_addextendedproperty 
@name = N'MS_Description', @value = @Table.ColumnDescription,
@level0type = N'Schema', @level0name = 'dbo',
@level1type = N'Table', @level1name = @Table.TableName, 
@level2type = N'Column', @level2name = @Table.ColumnName

New script

CREATE TABLE updateTable (TableName SYSNAME, ColumnName varchar(100), ColumnDescription varchar(100))

INSERT INTO updateTable VALUES('tblHAEMATOLOGY_MDT', 'MDT_ID', 'row1 test run')
INSERT INTO updateTable VALUES('tblHAEMATOLOGY_MDT', 'MEETING_ID', 'row2 test run')

DECLARE @TableName SYSNAME, @ColumnName varchar(100), @ColumnDescription varchar(100)
DECLARE @UpdateCursor CURSOR

SET @UpdateCursor = CURSOR FOR
SELECT TableName, ColumnName, ColumnDescription FROM updateTable

OPEN @UpdateCursor

FETCH NEXT FROM @UpdateCursor INTO @TableName, @ColumnName, @ColumnDescription

WHILE @@FETCH_STATUS = 0
BEGIN

IF EXISTS 
(select 
    sc.name,
    sep.value
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
                                 and sc.column_id = sep.minor_id
                                 and sep.name = 'MS_Description'
left join updateTable on st.name = @TableName and sc.name = @ColumnName
where sep.value is not null)

BEGIN

EXEC sp_updateextendedproperty 
@name = N'MS_Description', @value = @ColumnDescription,
@level0type = N'Schema', @level0name = 'dbo', 
@level1type = N'Table',  @level1name = @TableName, 
@level2type = N'Column', @level2name = @ColumnName

FETCH NEXT FROM @UpdateCursor INTO @TableName, @ColumnName, @ColumnDescription

END

ELSE

BEGIN

EXEC sp_addextendedproperty 
    @name = N'MS_Description', @value = @ColumnDescription,
    @level0type = N'Schema', @level0name = 'dbo',
    @level1type = N'Table', @level1name = @TableName, 
    @level2type = N'Column', @level2name = @ColumnName

FETCH NEXT FROM @UpdateCursor INTO @TableName, @ColumnName, @ColumnDescription

END

END

CLOSE @UpdateCursor
DEALLOCATE @UpdateCursor

score 1 · Accepted Answer · answered Mar 18 '16 at 16:42

1

This is one of the very few times that I would say this, but the solution is to use a cursor. Just create a cursor loop against your table variable and run your exists query inside the loop. I've done this very exact same logic before and it should work fine.

answered Mar 18 '16 at 16:42

Bruce Dunwiddie

2,888
1
15
20

ok i took your advice and wrote the above script but now i get this error - Msg 15217, Level 16, State 2, Procedure sp_updateextendedproperty, Line 36 Property cannot be updated or deleted. Property 'MS_Description' does not exist for 'dbo.tblHAEMATOLOGY_MDT.MEETING_ID'. - have i got a syntax error somewhere? – stonypaul Mar 18 '16 at 18:10
I don't see anything offhand and I don't have the setup in front of me right now to run the code. for now, comment out your calls to the stored procedures and put in select statements so you can see what things are evaluating to. return the results from the exists query, and put in a "select 'update', @TableName, @ColumnName", and one for the insert. my best guess is that there's something wrong with your extended_properties join, but nothing looks off. – Bruce Dunwiddie Mar 18 '16 at 18:22
this was a data issue, but after sorting that, the cursor option iterating through a temporary table works like a dream - thanks buddy – stonypaul Mar 21 '16 at 14:03

extended property mass update using temporary table

1 Answers1