Blog

Ever had the need to replace a piece of code in multiple SQL Server objects (Stored Procedure, Trigger, Function or Views)? Here’s a method to search for a string in all objects, replace it with some other code, and to finally script all changes. In other words, a method for mass editing SQL Server objects.

In a recent database version upgrade project, I ran into the issue of some deprecated syntax of the RAISERROR command. In short, before SQL Server 2012 the syntax “RAISERROR @ErrNo @ErrMsg” was accepted by SQL Server. From SQL Server 2012, a syntax error is raised. (Interestingly this error is not reported by the upgrade advisor, see the connect item).

Anyhow. I searched the database to find all the objects where the old RAISERROR syntax was used, and several hundred objects listed… One way is of course to address this problem manually. However, I wanted to try to create a more scripted solution. Who knows, I might run into some similar problem in the future.

Basically, the solution searches the sys.sql_modules table for the old RAISERROR syntax and saves the resulting objects into a table variable. Then it loops through the objects and creates a string with the necessary ALTER statement, and prints it to the screen. Just copy and run the results in a new query window. Thanks goes to this blog post for a solution to overcome the max 8000 (4000) character problem of the PRINT statement.

A word of warning though. I have tested this solution for one two (see update below) scenarios only. Please test and verify the change script thoroughly in your own environment. Note that you need to create the LongPrint procedure beforehand.

SET NOCOUNT ON

DECLARE
	@COUNTER INT = 1
	,@SQL NVARCHAR(MAX)
	,@ANSI_NULL BIT
	,@Q_ID BIT

DECLARE @TBL_OBJECTS TABLE
	(
		COL_Id INT IDENTITY(1,1) NOT NULL
		,COL_object_id INT NOT NULL
		,COL_definition NVARCHAR(MAX) NOT NULL
		,COL_uses_ansi_nulls BIT NOT NULL
		,COL_uses_quoted_identifier BIT NOT NULL
	)


INSERT @TBL_OBJECTS (COL_object_id, COL_definition, COL_uses_ansi_nulls, COL_uses_quoted_identifier)
SELECT object_id, definition, uses_ansi_nulls, uses_quoted_identifier
FROM 
	sys.sql_modules
WHERE
	/* Replace with your own filter */
	[definition] like '%raiserror @errno @errmsg%'

WHILE @COUNTER <= (SELECT COUNT(*) FROM @TBL_OBJECTS) BEGIN
	SELECT @ANSI_NULL = COL_uses_ansi_nulls, @Q_ID = COL_uses_quoted_identifier FROM @TBL_OBJECTS WHERE COL_Id = @COUNTER

	SELECT @SQL = CASE WHEN @ANSI_NULL = 1 THEN 'SET ANSI_NULLS ON' ELSE 'SET ANSI_NULLS OFF' END + CHAR(10) + 'GO' + CHAR(10)
	SELECT @SQL = @SQL + CASE WHEN @Q_ID = 1 THEN 'SET QUOTED_IDENTIFIER ON' ELSE 'SET QUOTED_IDENTIFIER OFF' END + CHAR(10) + 'GO' + CHAR(10)

	--PRINT @SQL

	SELECT @SQL = @SQL + COL_definition FROM @TBL_OBJECTS WHERE COL_Id = @COUNTER

	/* Replace with your own criteria */
	SELECT @SQL = REPLACE(@SQL, 'raiserror @errno @errmsg', ';THROW @ERRNO, @ERRMSG, 1')
	
	SELECT @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
	SELECT @SQL = REPLACE(@SQL, 'CREATE PROC', 'ALTER PROCEDURE')
	SELECT @SQL = REPLACE(@SQL, 'CREATE VIEW', 'ALTER VIEW')
	SELECT @SQL = REPLACE(@SQL, 'CREATE FUNCTION', 'ALTER FUNCTION')
	SELECT @SQL = REPLACE(@SQL, 'CREATE TRIGGER', 'ALTER TRIGGER')
	
	SELECT @SQL = @SQL + CHAR(10) + 'GO'
	SELECT @SQL = @SQL + CHAR(10) + CHAR(10) + CHAR(10)

	EXEC dbo.LongPrint @SQL

	SET @COUNTER = @COUNTER + 1
END

[blue_box]
*** Update ***

Oddly, you might have to run the generated SQL Script twice for the changes to be reflected in the sys.sql_modules table. There is a closed connect item about that.
[/blue_box]

[blue_box]
*** Update ***

Added an extra CRLF before ending GO statement.
[/blue_box]

[blue_box]
*** Update ***

Change all QUOTED_IDENTIFIER OFF or ANSI_NULL OFF to ON

I used a modified version of the script above to generate a change script for all database objects that was created with the setting QUOTED_IDENTIFIER or ANSI_NULL OFF. The new setting is ON. The reason those settings must be ON is that I want to be able to use some newer features in SQL Server, such as filtered indexes.

SET NOCOUNT ON
 
DECLARE
    @COUNTER INT = 1
    ,@SQL NVARCHAR(MAX)
    ,@ANSI_NULL BIT
    ,@Q_ID BIT
 
DECLARE @TBL_OBJECTS TABLE
    (
        COL_Id INT IDENTITY(1,1) NOT NULL
        ,COL_object_id INT NOT NULL
        ,COL_definition NVARCHAR(MAX) NOT NULL
        ,COL_uses_ansi_nulls BIT NOT NULL
        ,COL_uses_quoted_identifier BIT NOT NULL
    )
 
 
INSERT @TBL_OBJECTS (COL_object_id, COL_definition, COL_uses_ansi_nulls, COL_uses_quoted_identifier)
SELECT object_id, definition, uses_ansi_nulls, uses_quoted_identifier
FROM
    sys.sql_modules
WHERE
    /* Replace with your own filter */
	(uses_ansi_nulls = 0 OR uses_quoted_identifier = 0)

WHILE @COUNTER <= (SELECT COUNT(*) FROM @TBL_OBJECTS) BEGIN
    SELECT @ANSI_NULL = COL_uses_ansi_nulls, @Q_ID = COL_uses_quoted_identifier FROM @TBL_OBJECTS WHERE COL_Id = @COUNTER
 
    SELECT @SQL = CASE WHEN @ANSI_NULL = 1 THEN 'SET ANSI_NULLS ON' ELSE 'SET ANSI_NULLS ON' END + CHAR(10) + 'GO' + CHAR(10)
    SELECT @SQL = @SQL + CASE WHEN @Q_ID = 1 THEN 'SET QUOTED_IDENTIFIER ON' ELSE 'SET QUOTED_IDENTIFIER ON' END + CHAR(10) + 'GO' + CHAR(10)

    --PRINT @SQL
 
    SELECT @SQL = @SQL + COL_definition FROM @TBL_OBJECTS WHERE COL_Id = @COUNTER
 
    /* Replace with your own criteria */
    --SELECT @SQL = REPLACE(@SQL, 'raiserror @errno @errmsg', ';THROW @ERRNO, @ERRMSG, 1')
     
    SELECT @SQL = REPLACE(@SQL, 'CREATE PROCEDURE', 'ALTER PROCEDURE')
    SELECT @SQL = REPLACE(@SQL, 'CREATE PROC', 'ALTER PROCEDURE')
    SELECT @SQL = REPLACE(@SQL, 'CREATE VIEW', 'ALTER VIEW')
    SELECT @SQL = REPLACE(@SQL, 'CREATE FUNCTION', 'ALTER FUNCTION')
    SELECT @SQL = REPLACE(@SQL, 'CREATE TRIGGER', 'ALTER TRIGGER')
     
    SELECT @SQL = @SQL + CHAR(10) + 'GO'
    SELECT @SQL = @SQL + CHAR(10) + CHAR(10) + CHAR(10)
 
    EXEC dbo.LongPrint @SQL
 
    SET @COUNTER = @COUNTER + 1
END