CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008stored-procedures
Dinesh Reddy Alla
Dinesh Reddy Alla

Reputation: 1717

How to update a datatype for a common parameter in all procedures in selected Database

Is there any chance to update datatype value in all procedures at a time in particular database for example i have procedures with credentials like

@password varchar(50),
@employeeEmail varchar(150)

and now i need to update '@password' like 

@password varchar(100),
@employeeEmail varchar(150)

Upvotes: 1

Views: 65

Answers (2)

M.Ali
M.Ali

Reputation: 69564

An attempt.....

The following will produce a script to alter all the target procedure only where

1 - Procedure is not a System Stored Procedure (i.e name doesnt start with sp_).
2 - Procedure has a Parameter called @password.

... Hoping that you have not got any User Defined Procedure Names starting with sp_.

Once the script is created have a very careful look, at the script. Backup your database, Once happy only then execute the script. Happy days. 

 SET NOCOUNT ON;

-- Table to hold names of all the user defined Procedures
CREATE TABLE #UD_Procedures (Proc_Num INT IDENTITY(1,1), Proc_Name NVARCHAR(128))
GO

-- Table to hold Definitions of all the user defined Procedures
CREATE TABLE #UD_Proc_Definitinos (Def NVARCHAR(MAX))
GO

-- Populate Procedure Names table
INSERT INTO #UD_Procedures
SELECT OBJECT_NAME([OBJECT_ID])
 FROM sys.all_parameters ap
WHERE EXISTS  (SELECT 1
               FROM sys.procedures p 
               WHERE LEFT(name, 3) <> 'sp_'
                 AND OBJECT_NAME(ap.[object_id]) = p.name)
AND ap.Name = '@password'
GO


    DECLARE @Proc_Name NVARCHAR(128);

WHILE EXISTS(SELECT 1 FROM #UD_Procedures)
 BEGIN


     SELECT TOP 1 @Proc_Name = Proc_Name 
     FROM #UD_Procedures 
     ORDER BY Proc_Num

     INSERT INTO #UD_Proc_Definitinos
     EXECUTE sp_helptext @Proc_Name

     INSERT INTO #UD_Proc_Definitinos
     VALUES ('GO', ' ', ' ')

     DELETE FROM #UD_Procedures WHERE Proc_Name = @Proc_Name
 END


UPDATE #UD_Proc_Definitinos
 SET Def =    REPLACE(
                REPLACE(
                  REPLACE(
                     Def
                        ,'CREATE PROCEDURE', 'ALTER PROCEDURE')
                          ,'CREATE PROC', 'ALTER PROCEDURE')
                            ,'@password varchar(50)', ' @password varchar(100) '
                          )

-- Now select "Result to text file" and execute the following 

SELECT * FROM #UD_Proc_Definitinos

-- Save the file and Copy paste into SSMS and execute,,, 
-- just be on safe side backup your DB before you do anything :)

--  DROP temp tables 

DROP TABLE #UD_Procedures
GO
DROP TABLE #UD_Proc_Definitinos
GO

Upvotes: 0

OttO
OttO

Reputation: 419

Well this is a simple solution, find all procedures that contain the search string, loop through them and replace that string in the definition and run it. This is a very simplistic solution with no error checking or transactions but show what can be done

DECLARE @searchstr VARCHAR(50), @replacestr VARCHAR(50), @str VARCHAR(MAX), @name VARCHAR(40)
SET @searchstr = '@password varchar(50),'
SET @replacestr = '@password varchar(100),'

DECLARE test_cursor CURSOR READ_ONLY FOR 
SELECT name, object_definition(OBJECT_ID)
FROM sys.objects
WHERE object_definition(OBJECT_ID) LIKE '%' + @searchstr + '%'
and type = 'P'

OPEN test_cursor

FETCH NEXT FROM test_cursor INTO @name, @str
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        PRINT @name
        SET @str = REPLACE(@str, @searchstr, @replacestr)
        EXEC ('drop proc ' + @name)
        EXEC (@str)
    END
    FETCH NEXT FROM test_cursor INTO @name, @str
END

CLOSE test_cursor
DEALLOCATE test_cursor

Upvotes: 1

Related Questions