I try to add extended properties to my table and column, but since SQL Server separates the add and update stored procedure, and need too many unused parameters for my case, I decide to create wrapper for these 2 stored procedures so I can create description with only 3 parameters needed for add or update on table and column level. This my current code:
CREATE PROCEDURE sp_addorupdatedesc
@tableName varchar,
@columnName varchar = NULL,
@objectDescription varchar
AS
BEGIN
IF (@columnName IS NULL)
BEGIN
IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL) WHERE OBJNAME=@tableName)
BEGIN
EXECUTE sp_addextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, default, NULL
END
ELSE
BEGIN
EXECUTE sp_updateextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, default, NULL
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT 1
FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class = 1 AND T.NAME=@tableName AND C.name = @columnName)
BEGIN
EXECUTE sp_addextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, 'column', @columnName
END
ELSE
BEGIN
EXECUTE sp_updateextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, 'column', @columnName
END
END
END
GO
But when I use this stored procedure, I get this error instead:
Msg 15135, Level 16, State 8, Procedure sp_addextendedproperty, Line 58
Object is invalid. Extended properties are not permitted on 'dbo.P.P', or the object does not exist.
I changed my stored procedure as marc_S suggested and it works now.
This may be a clumsy mistake, but I hope if anyone need to simplify add or update extended property to table or column in one stored procedure with only 3 parameters "TableName", "ColumnName", "Description".
Here is the code.
CREATE PROCEDURE setdescription
@tableName varchar(100),
@columnName varchar(100) = NULL,
@objectDescription varchar(250)
AS
BEGIN
IF (@columnName IS NULL)
BEGIN
IF NOT EXISTS (SELECT 1 FROM fn_listextendedproperty (NULL, 'user', 'dbo', 'table', default, NULL, NULL) WHERE OBJNAME=@tableName)
BEGIN
EXECUTE sp_addextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, default, NULL
END
ELSE
BEGIN
EXECUTE sp_updateextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, default, NULL
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT 1 FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE class = 1 AND T.NAME=@tableName AND C.name = @columnName)
BEGIN
EXECUTE sp_addextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, 'column', @columnName
--EXECUTE sp_addextendedproperty @name=N'CXC_DESCRIPTION', @value=N@temp3, @level0type=N'user', @level0name=N'dbo', @level1type=N'table', @level1name=N@temp1, @level2type=N'column', @level2name=N@temp2
END
ELSE
BEGIN
EXECUTE sp_updateextendedproperty 'MY_DESCRIPTION', @objectDescription, 'user', dbo, 'table', @tableName, 'column', @columnName
END
END
END
GO