January 30, 2008 at 8:13 am
I have been trying to discipline myself, and add an extended property named "Description" to any new objects (procedures, functions, views) I create.
Well, so much for documentation; I just realized that whenever I update anything with an ALTER statement the "Description" (all extended properties?) get discarded. Tell me that makes sense (or is wrong). Any work around?
January 30, 2008 at 8:30 am
Jim Russell (1/30/2008)
I have been trying to discipline myself, and add an extended property named "Description" to any new objects (procedures, functions, views) I create.Well, so much for documentation; I just realized that whenever I update anything with an ALTER statement the "Description" (all extended properties?) get discarded. Tell me that makes sense (or is wrong). Any work around?
Hi Jim,
alter statements should not remove extended properties. Could you give an example please?
What happens frequently, is that if the object cannot be altered (e.g. you change the return table schema for a table returning function), the object is dropped and then created, and in such cases the extended properties go. (but you would need to do this manually)
Regards,
Andras
January 30, 2008 at 10:50 am
Thanks for your reply.
To provide an example, I built this script:
IF OBJECT_ID('[DBO].[TstFn]') IS NOT NULL
DROP FUNCTION [DBO].[TstFn]
GO
CREATE FUNCTION DBO.TstFn ()
RETURNS INT
AS
BEGIN
RETURN(1)
END;
GO
SELECT VALUE FROM SYS.EXTENDED_PROPERTIES
WHERE MAJOR_ID = OBJECT_ID('DBO.TstFn')
and name = 'Description'
EXEC sys.sp_addextendedproperty @name=N'Description'
, @value=N'This is the description'
, @level0type=N'SCHEMA'
, @level0name=N'dbo'
, @level1type=N'FUNCTION'
, @level1name=N'TstFn'
GO
SELECT VALUE FROM SYS.EXTENDED_PROPERTIES
WHERE MAJOR_ID = OBJECT_ID('DBO.TstFn')
and name = 'Description'
GO
ALTER FUNCTION DBO.TstFn ()
RETURNS INT
AS
BEGIN
RETURN(2)
END;
GO
SELECT VALUE FROM SYS.EXTENDED_PROPERTIES
WHERE MAJOR_ID = OBJECT_ID('DBO.TstFn')
AND NAME = 'Description'
which shows things working just as you say they should!!
My specific problem was with a table returning function, which I will test by changing the function definition above to use a table returning function (the syntax of which gives me a headache, and will take a while.)
However, I noted the extended property getting lost when I updated the following (stolen, with changes, from a posting today.) (I was adding and inspecting the extended properties from the SSMS interface.) While I try to update my test script to use a table returning function, perhaps you can see something in the following that might be responsible for what I saw:
ALTER FUNCTION
[dbo].[changesSince]
(
@sdtInputDate SMALLDATETIME
)
RETURNS @tblModifiedObjectsSince
TABLE
( ObjectType VARCHAR(20)
,ObjectName VARCHAR(500)
,CerateDate DATETIME
,ModifiedDate DATETIME
,Description VARCHAR(MAX)
,Script VARCHAR(MAX)
)
BEGIN
INSERT @tblModifiedObjectsSince
SELECT 'Stored Procedure'
, P.name
, P.create_date
, P.modify_date
, CONVERT(VARCHAR(MAX),x.value)as [Description]
, M.definition
FROM sys.sql_modules M
left outer join sys.extended_properties X
on m.object_id = x.major_id
and x.name = 'Description'
INNER JOIN sys.procedures P
ON M.object_id = P.object_id
AND CONVERT(SMALLDATETIME, CAST(DATEPART(MONTH, P.modify_date)AS VARCHAR(2)) + '-' + CAST(DATEPART(DAY, P.modify_date) AS VARCHAR(2)) + '-' + CAST(DATEPART(YEAR, P.modify_date)AS VARCHAR(4)), 101) > @sdtInputDate
INSERT @tblModifiedObjectsSince
SELECT 'View'
, V.name
, V.create_date
, V.modify_date
, CONVERT(VARCHAR(MAX),x.value)as [Description]
, M.definition
FROM sys.sql_modules M
left outer join sys.extended_properties X
on m.object_id = x.major_id
and x.name = 'Description'
INNER JOIN sys.views V
ON M.object_id = V.object_id
AND CONVERT(SMALLDATETIME, CAST(DATEPART(MONTH, V.modify_date)AS VARCHAR(2)) + '-' + CAST(DATEPART(DAY, V.modify_date) AS VARCHAR(2)) + '-' + CAST(DATEPART(YEAR, V.modify_date)AS VARCHAR(4)), 101) > @sdtInputDate
RETURN
END
...or perhaps I will see what dumb thing I am doing in the meantime.
January 30, 2008 at 11:03 am
Here is the script modified (I think) to work with a table returning function. This time (unless I am doing something dumb) it does seem to show the Extended Property being lost. (I also added a statement to show the empty value before I add one.)
But I am delighted to find that this doesn't seem to happen (as you suggested) to all DB Objects.
IF OBJECT_ID('[DBO].[TstFn]') IS NOT NULL
DROP FUNCTION [DBO].[TstFn]
GO
CREATE FUNCTION DBO.TstFn ()
RETURNS @TstFnTbl TABLE (C1 INT)
AS
BEGIN
INSERT @TstFnTbl
SELECT 1
RETURN
END;
GO
SELECT VALUE FROM SYS.EXTENDED_PROPERTIES
WHERE MAJOR_ID = OBJECT_ID('DBO.TstFn')
and name = 'Description'
EXEC sys.sp_addextendedproperty @name=N'Description'
, @value=N'This is the description'
, @level0type=N'SCHEMA'
, @level0name=N'dbo'
, @level1type=N'FUNCTION'
, @level1name=N'TstFn'
GO
SELECT VALUE FROM SYS.EXTENDED_PROPERTIES
WHERE MAJOR_ID = OBJECT_ID('DBO.TstFn')
and name = 'Description'
GO
ALTER FUNCTION DBO.TstFn ()
RETURNS @TstFnTbl TABLE (C1 INT)
AS
BEGIN
INSERT @TstFnTbl
SELECT 2
RETURN
END;
GO
SELECT VALUE FROM SYS.EXTENDED_PROPERTIES
WHERE MAJOR_ID = OBJECT_ID('DBO.TstFn')
AND NAME = 'Description'
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply