I want to share a “trick” on how to mark SQL Server user objects as deprecated. But first, why we need to mark an object as deprecated?
- code refactors
- object schema refactors
- mandatory backward compatibility in a multi customer scenario
- sp_addextendedproperty – adds an extended property
- sp_dropextendedproperty – deletes an extended property
- sp_updateextendedproperty – updates an extended property
Scenario
Suppose that we have a set of stored procedures, functions and table types (aka programmability objects). Now, we want to mark some of them as deprecated, starting from the new major versions of our software. The following script creates the list of sample objects:
USE Utilities;
GO
IF EXISTS(SELECT 1 FROM sys.types T WHERE name = 'tabletype1' AND schema_id = 1)
DROP TYPE dbo.tabletype1;
GO
CREATE TYPE dbo.tabletype1 AS TABLE (id int, val varchar(30));
GO
IF EXISTS(SELECT 1 FROM sys.types T WHERE name = 'tabletype2' AND schema_id = 1)
DROP TYPE dbo.tabletype2;
GO
CREATE TYPE dbo.tabletype2 AS TABLE (id int, val varchar(30), num decimal(18,2));
GO
IF EXISTS(SELECT 1 FROM sys.objects O WHERE object_id = object_id('dbo.procedure1'))
DROP PROC dbo.procedure1;
GO
CREATE PROC dbo.procedure1
AS
BEGIN
SELECT 1
END;
GO
IF EXISTS(SELECT 1 FROM sys.objects O WHERE object_id = object_id('dbo.procedure2'))
DROP PROC dbo.procedure2;
GO
CREATE PROC dbo.procedure2
AS
BEGIN
SELECT 2
END;
GO
IF EXISTS(SELECT 1 FROM sys.objects O WHERE object_id = object_id('dbo.function1'))
DROP FUNCTION dbo.function1;
GO
CREATE FUNCTION dbo.function1()
RETURNS int
AS
BEGIN
RETURN 1
END;
GO
IF EXISTS(SELECT 1 FROM sys.objects O WHERE object_id = object_id('dbo.function2'))
DROP FUNCTION dbo.function2;
GO
CREATE FUNCTION dbo.function2()
RETURNS int
AS
BEGIN
RETURN 2
END;
GO
Now, we’re going to mark all the objects with the suffix “2” as deprecated. The naming convention we will use is:
- Property name: Utilities_Deprecation_IsDeprecated – Value: True
- Property name: Utilities_Deprecation_ValidUntilVersion – Value: 2.0
- Property name: Utilities_Deprecation_Message – Value: This object will be removed starting from version 2.0 of the database, use the object with the same name and with a “1” suffix
With the sp_addextendedproperty we’ll add the properies:
EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_IsDeprecated', @value = N'True', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'procedure2'; GO EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_IsDeprecated', @value = N'True', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'FUNCTION', @level1name = 'function2'; GO EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_IsDeprecated', @value = N'True', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TYPE', @level1name = 'tabletype2'; GO EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_ValidUntilVersion', @value = N'2.0', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'procedure2'; GO EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_ValidUntilVersion', @value = N'2.0', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'FUNCTION', @level1name = 'function2'; GO EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_ValidUntilVersion', @value = N'2.0', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TYPE', @level1name = 'tabletype2'; GO EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_Message', @value = N'This object will be removed starting from version 2.0 of the database, use the object with the same name and with a "1" suffix', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'PROCEDURE', @level1name = 'procedure2'; GO EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_Message', @value = N'This object will be removed starting from version 2.0 of the database, use the object with the same name and with a "1" suffix', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'FUNCTION', @level1name = 'function2'; GO EXEC sys.sp_addextendedproperty @name = N'Utilities_Deprecation_Message', @value = N'This object will be removed starting from version 2.0 of the database, use the object with the same name and with a "1" suffix', @level0type = N'SCHEMA', @level0name = 'dbo', @level1type = N'TYPE', @level1name = 'tabletype2'; GO
The objects are marked. How can we get the list of those objects? How can we give a report or a source dataset for an application?
We can inspect the extended property tab on each object, or we can write a simple query, like the following one:
</span></div>
<div>
<pre>SELECT
[Schema] = S.name
, [Name] = O.name
, EPName = EP.name
, EP.value
, O.type_desc
FROM
sys.extended_properties EP
JOIN sys.objects O ON O.object_id = EP.major_id
JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE
EP.name IN ('Utilities_Deprecation_IsDeprecated', 'Utilities_Deprecation_ValidUntilVersion', 'Utilities_Deprecation_Message')
UNION ALL
SELECT
[Schema] = S.name
, [Name] = T.name
, EPName = EP.name
, EP.value
, type_desc = 'TYPE'
FROM
sys.extended_properties EP
JOIN sys.types T ON T.user_type_id = EP.major_id
JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE
EP.name IN ('Utilities_Deprecation_IsDeprecated', 'Utilities_Deprecation_ValidUntilVersion', 'Utilities_Deprecation_Message');
Schema Name EPName value ---------- --------------------- ------------------------------------------ -------------------------------------------------------- dbo procedure2 Utilities_Deprecation_IsDeprecated True dbo procedure2 Utilities_Deprecation_ValidUntilVersion 2.0 dbo procedure2 Utilities_Deprecation_Message This object will be removed starting from version 2.0... dbo function2 Utilities_Deprecation_IsDeprecated True dbo function2 Utilities_Deprecation_ValidUntilVersion 2.0 dbo function2 Utilities_Deprecation_Message This object will be removed starting from version 2.0... dbo tabletype2 Utilities_Deprecation_IsDeprecated True dbo tabletype2 Utilities_Deprecation_ValidUntilVersion 2.0 dbo tabletype2 Utilities_Deprecation_Message This object will be removed starting from version 2.0...
In addition to the system stored procedure described above, we can use also a system function called sys.fn_listextendedproperty that lists all the extended properties at certain level. But the filter is not so customizable.
With few simple operations it is possible to have an object deprecation list. This could be very important if we want to track refactors, to give backward compatibility and to return a message on “when the object will expire”. In a multi customer environment, this can be very useful. As we can see, extended properties can be used for custom extended implementations. Watching deeply, SQL Server already uses that in order to write descriptions and views entities positions. Some third party tools uses them for documenting database, writing database revisions, link with the source control, and so on.
Extended properties can be a good starting point to work with for our custom implementation on SQL Server.
Stay Tuned! 🙂
Alessandro Alpi's Blog 