How to mark SQL Server objects as deprecated with extended properties

SQL Server 4 Minutes

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
We can use extended properties. They are a set of name/value properties in which you can specify custom value, with your own naming convention. Every SQL Server object can be, let’s say, extended using those properties. There are also three stored procedures which you can use in order to manage extended properties:

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:

  1. Property name: Utilities_Deprecation_IsDeprecated – Value: True
  2. Property name: Utilities_Deprecation_ValidUntilVersion – Value: 2.0
  3. 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! 🙂

Unknown's avatar

Published by suxstellino (Alessandro Alpi)

http://alessandroalpi.blog

Published

2 thoughts on “How to mark SQL Server objects as deprecated with extended properties”

  1. An interesting idea would be to create DDL triggers which inspect the referenced objects for these extended properties. If any deprecation EPs are found, the trigger could fire a warning (or error) message. I believe these triggers should only exist in the dev environment. A CI/CD pipeline could monitor the number of warnings emitted, and a team could direct their efforts to minimize them.

    Reply

Leave a comment