Problem Statement
After SQL Server Migration or database rename or object rename, the dependent objects such as SP’s,linked Servers require modification. Is there a best way to update names in all the stored procedures instead of manually checking and updating? Or how do we handle linked servers; by creating an alias?. How do we ensure and validate the SP’s or Can we take a backup of those procedures out of ‘n’ of SP’s?
Solution
Yes, We can take a backup of those SP's where it requires modification also for linked servers we can create alias.
The step by step details are given below
- Generate script of all stored procedures - You can use the scripting wizrd to generate the script. Right-click the db –> tasks –> Generate scripts –> go through the wizard. The requirement is to generate for specific SP's where it meets the search string pre-requisite.
- Generate an updated script - The same script is used to update all the eligible SP's with replace function.
- Create alias for linked servers
Generate script for matching search string of all SP's
The below T-SQL generates script for SP's which satisfies the search criteria.
Using sp_helptext
Replace the @SearchFor parameter in the below SQL's and execute the code,
-
- SET NOCOUNT ON
- DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-
- SET @searchFor = 'line'
-
- DECLARE @temp TABLE (spText VARCHAR(MAX))
- DECLARE curHelp CURSOR FAST_FORWARD
- FOR
-
-
- SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
- FROM syscomments WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'
- ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
- OPEN curHelp
- FETCH next FROM curHelp INTO @sqlToRun
- WHILE @@FETCH_STATUS = 0
- BEGIN
-
- INSERT INTO @temp
- EXEC (@sqlToRun)
-
- INSERT INTO @temp
- VALUES ('GO')
- FETCH next FROM curHelp INTO @sqlToRun
- END
- CLOSE curHelp
- DEALLOCATE curHelp
- SELECT spText FROM @temp
-
-
- GO
Using system view sys.procedures
Replace the @SearchFor parameter in the below SQL's and execute the code,
- SET NOCOUNT ON
- DECLARE @Test TABLE (Id INT IDENTITY(1,1), Code varchar(max))
- DECLARE @searchFor VARCHAR(100)
- SET @searchFor = 'Line'
- INSERT INTO @Test (Code)
- SELECT 'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL
- DROP PROCEDURE ['+ schema_name(schema_id) +' ].[' + Name + ']' + char(13) + char(10) + 'GO' + char(13) +char(10) +
- OBJECT_DEFINITION(OBJECT_ID) + char(13) +char(10) + 'GO' + char(13) + char(10)
- from sys.procedures
- where is_ms_shipped = 0 and OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+@searchFor+'%'
- DECLARE @lnCurrent int, @lnMax int
- DECLARE @LongName varchar(max)
- SELECT @lnMax = MAX(Id) FROM @Test
- SET @lnCurrent = 1
- WHILE @lnCurrent <= @lnMax
- BEGIN
- SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent
- WHILE @LongName <> ''
- BEGIN
- print LEFT(@LongName,8000)
- SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
- END
- SET @lnCurrent = @lnCurrent + 1
- END
Generate modified SP's script
Replace the @SearchFor and @replacewith parameter in the below SQL's and execute the code. The output is copied into SSMS console and execute it to update all the SP's.
-
- SET NOCOUNT ON
- DECLARE @sqlToRun VARCHAR(1000), @searchFor VARCHAR(100), @replaceWith VARCHAR(100)
-
- SET @searchFor = '[MY-SERVER]'
-
- SET @replaceWith = '[MY-SERVER2]'
-
- DECLARE @temp TABLE (spText VARCHAR(MAX))
- DECLARE curHelp CURSOR FAST_FORWARD
- FOR
-
-
- SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
- FROM syscomments WHERE TEXT LIKE '%' + REPLACE(REPLACE(@searchFor,']','\]'),'[','\[') + '%' ESCAPE '\'
- ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '
- OPEN curHelp
- FETCH next FROM curHelp INTO @sqlToRun
- WHILE @@FETCH_STATUS = 0
- BEGIN
-
- INSERT INTO @temp
- EXEC (@sqlToRun)
-
- INSERT INTO @temp
- VALUES ('GO')
- FETCH next FROM curHelp INTO @sqlToRun
- END
- CLOSE curHelp
- DEALLOCATE curHelp
-
-
- UPDATE @temp
- SET spText = REPLACE(REPLACE(spText,'CREATE PROCEDURE', 'ALTER PROCEDURE'),@searchFor,@replaceWith)
- SELECT spText FROM @temp
-
-
- GO
Create Linked Server Alias
Step 1
- In SQL Server Management Studio, open Linked Servers and then 'New Linked Server'.
- Inside of appeared wizard, select the General tab.
- Specify alias name in "Linked server" field.
- Select SQL Native Client as provider.
- Add sql_server in "Product Name" field (that's the magic).
- In "Data Source", specify name of the host to be used as linked server.
Step 2
In Security tab, specify proper security options (e.g. security context).
Step 3
In Server Options tab, put "Data Access", RPC, "Rpc Out" and "Use Remote Collaboration" to be true.
Conclusion
- Time Saving - Identifying and modifying many objects is going to be a tedious job. The script makes life easier. Migration is part of the evolution but think of updating SP's, Its really important to have a backup and easy if some automation like this able to modify what is needed
- Easy to run and generate scripts for SP's based on search string
- Easy to keep track of modified SP's and efficient way to rollback as it generates script for all eligible SP's
References
- http://www.ideosity.com/ourblog/post/ideosphere-blog/2013/06/14/how-to-find-and-replace-text-in-all-stored-procedures
- http://alexpinsker.blogspot.com/2007/08/how-to-give-alias-to-sql-linked-server.html
- http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-script-all-stored-procedures-in-a/