May 19, 2014 at 7:36 am
I'm trying to update the Extended Properties (EP) of some columns, but regardless as to whether I update them manually (by going to the properties using the GUI) or using sp_updateextendedproperty, I end up with a duplicate EP for the given column when I run a query to return the EP's for the table!
For example, I have a Sales table and I'm trying to change the EP 'value' for the CustomerCode column from 'This is the customer code' to 'This is the customer code (see reference table: CustomerCodeRefer)."
When I try with either of the methods I listed above, and then query the EP's for the Sales table, I get two rows returned for the CustomerCode column, one with the old 'value' and one with the new 'value'. The weird thing is when I go in through the GUI to the properties of the CustomerCode column, I only see the updated verbiage in the 'value'.
Any help would be greatly appreciated!
May 19, 2014 at 12:15 pm
Can you post your query that returns the Extended Properties? I can't duplicate the behavior, so I'm wondering if it is a problem with the query.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 19, 2014 at 12:39 pm
Sure thing, see below (sorry about the weird formatting with the copy/paste):
SELECT DISTINCT TOP (100) PERCENT i.TABLE_CATALOG AS [database_name]
, i.TABLE_SCHEMA AS [schema_name]
, a.name AS [table_name]
, b.name AS [column_name]
, (CASE
WHEN DATA_TYPE IN ('char', 'varchar', 'nvarchar', 'text')
THEN DATA_TYPE + '(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = - 1
THEN 'max'
ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(100)) END + ')'
WHEN DATA_TYPE IN ('numeric')
THEN DATA_TYPE + '(' + (CAST(NUMERIC_PRECISION AS VARCHAR(50))) + ',' + (CAST(NUMERIC_SCALE AS VARCHAR(50))) + ')'
ELSE DATA_TYPE END) AS [data_type]
, (CASE WHEN h.value IS NULL
THEN '-'
ELSE h.value END) AS description
FROM sys.sysobjects AS a
INNER JOIN sys.syscolumns AS b
ON a.id = b.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS i
ON a.name = i.TABLE_NAME
AND b.name = i.COLUMN_NAME
INNER JOIN sys.systypes AS c
ON b.xtype = c.xtype
LEFT OUTER JOIN (SELECT so.id, sc.colid, sc.name
FROM sys.syscolumns AS sc
INNER JOIN sys.sysobjects AS so
ON so.id = sc.id
INNER JOIN sys.sysindexkeys AS si
ON so.id = si.id
AND sc.colid = si.colid
WHERE (si.indid = 1)
) AS d
ON a.id = d.id AND b.colid = d.colid
LEFT OUTER JOIN sys.foreign_key_columns AS e
ON a.id = e.parent_object_id
AND b.colid = e.parent_column_id
LEFT OUTER JOIN sys.objects AS g
ON e.referenced_object_id = g.object_id
LEFT OUTER JOIN sys.extended_properties AS h
ON a.id = h.major_id
AND b.colid = h.minor_id
WHERE (a.type = 'U')
ORDER BY [schema_name], [table_name]
May 19, 2014 at 12:57 pm
I can't duplicate the behavior. I have used EXEC sys.sp_updateextendedproperty multiple times and manually in SSMS to update an extended property and your query is only returning on copy of it. I've tried this against SQL Server 2008R2 SP1 and SQL Server 2012 RTM.
Can you post your complete process including the updating of the extended property?
Also, you aren't returning the property name, so is it possible you have multiple extended properties on a column with different names?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply