How to mark SQL Server objects as deprecated with extended properties

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! :)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s