Sql-server – How to force the TSQL-Developers to use not deprecated TSQL

sql serversql server 2014t-sql

I want to avoid the using of deprecated code.

Like the new feature Nr. 8
http://msdn.microsoft.com/en-us/library/ms178053.aspx

I want to force them to use "WITH" for example, or a semicolon at the end.

Is there an easy way to force this with ease?

Best Answer

Bjorn,

There is nothing out of the box that will force the developers to use non-deprecated features (as they are deprecated, not removed). The most tailored solution that could be created is with DDL trigger(s), but note that they can be tricky... especially if not extremely familiar with them.

I would suggest PBM, but it has limitations that won't necessarily capture everything you're looking for. As already discussed, you can use extended events (in the previous link I had) to check for the use of the deprecated items.

Here is an example that will stop the use of fn_get_sql() and give a helpful message while stopping the use. Note that multiple checks can be made in the same trigger, this is just a trivial example.

/* Create a test datbase */
CREATE DATABASE No_Deprecated_Items;
GO

USE No_Deprecated_Items;
GO

CREATE TRIGGER Stop_Deprecated_Items
ON DATABASE
AFTER CREATE_FUNCTION, ALTER_FUNCTION, CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_TRIGGER, ALTER_TRIGGER
AS
BEGIN
    DECLARE @Command NVARCHAR(MAX)

    SELECT @Command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    IF(PATINDEX('%fn_get_sql%', @Command) > 0)
    BEGIN
        RAISERROR('fn_get_sql is deprecated, use sys.dm_exec_sql_text() instead!', 16, 1)
        ROLLBACK
    END
END
GO

/*
This is created as it passed the constraints in the trigger
*/

CREATE PROCEDURE Good_Procedure
AS
BEGIN

    DECLARE @RandomVariable INT

    SET @RandomVariable = 0

    SELECT * FROM sys.dm_exec_sql_text(@RandomVariable)

    Print 'COMPLETE!'
END
GO

-- See the SP is created
SELECT * FROM sys.procedures
GO

/*
This will be rolledback as it fails the trigger logic.
*/
CREATE PROCEDURE Bad_Procedure
AS
BEGIN

    DECLARE @RandomVariable INT

    SET @RandomVariable = 0

    SELECT * FROM ::fn_get_sql(@RandomVariable)

    Print 'COMPLETE!'
END
GO

-- See the SP is NOT created
SELECT * FROM sys.procedures
GO

http://msdn.microsoft.com/en-us/library/ms189799.aspx

http://msdn.microsoft.com/en-us/library/ms187909.aspx

http://msdn.microsoft.com/en-us/library/bb510452.aspx

Related Question