I need to find all sprocs having a misspelled word and dynamically correct.
Googled and found a solution here
Testing the non dynamic portion of it I'm getting error
(1 row affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
CODE finds, drops, creates new CREATE Procedure
use test
go
Declare @spname nvarchar(max)
Declare @moddef nvarchar(max)
SELECT
Replace ((REPLACE(definition,'subscriptonguid','subscriptionguid')),'ALTER','create')
FROM sys.sql_modules a
JOIN
( select type, name,object_id
from sys.objects b
where type in (
'p' -- procedures
)
and is_ms_shipped = 0
)b
ON a.object_id=b.object_id where b.name = 'HK'
exec('drop procedure dbo.' + @spname)
execute sp_executesql @moddefSPROC which has misspelled column "subscriptonguid"
Sample SPROC having misspelled column
use test
go
CREATE procedure [dbo].[HK]
as
begin
select top 10 subscriptonguid --missing the i in 'tion'
from test.dbo.Subscription
end
go
CREATE TABLE (misspelling is fixed)
USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Subscription](
[SubscriptionGUID] [varchar](255) NULL
) ON [PRIMARY]
GO
In real life as well I have a misspelled column name on a table that I want to correct: subscriptonguid ->subscriptionguid and then fix referencing sprocs
--Quote me
May 1, 2020 at 7:01 am
you can't blindly replace all "create" by an "alter".
Replace ((REPLACE(definition,'subscriptonguid','subscriptionguid')),'ALTER','create')
you need to replace the above bit of code with
above will fail if you are one of those that puts comments BEFORE the create procedure command - not much you can do about those other than manually change them (and take the opportunity to move the code to AFTER the create procedure)
but all this would be better done if you use Visual Studio.
May 1, 2020 at 9:35 am
The idea of doing a DROP & CREATE fills me with dread - all the bespoke permissions you could lose...
Thomas Rushton
blog: https://thelonedba.wordpress.com
May 1, 2020 at 12:07 pm
The idea of doing a DROP & CREATE fills me with dread - all the bespoke permissions you could lose...
Ditto that. Since this is a 2019 thread, I'm thinking that CREATE OR ALTER would be much safer. Haven't read the rest of this thread to know for sure, though.
--Jeff Moden
May 1, 2020 at 2:13 pm
+1
May 1, 2020 at 2:59 pm
Install this stored procedure
IF Object_Id(N'INFGenerateObjectScript', N'P') IS NULL
BEGIN
EXEC ('CREATE PROCEDURE [dbo].[INFGenerateObjectScript] AS BEGIN SELECT 1 END')
END
GO
GO
-- **********************************************************************
-- PROCEDURE INFGenerateObjectScript
-- Description: Generates the create script for all stored
-- procedures on a database.
-- The results are output to the messages portion of
-- the SQL server Management Studio screen
-- $Revision: 1.0 $
-- Usage:
-- EXEC INFGenerateObjectScript '%', 1, 1, 1
-- EXEC INFGenerateObjectScript 'INFGenerateObjectScript', 1, 1
-- **********************************************************************
ALTER PROCEDURE [dbo].INFGenerateObjectScript
(
@ProcName varchar(128) = '%',
@IncludeAuditTrail bit = 1,
@IncludeUseDatabase bit = 1,
@IncludeSeperators bit = 0,
@QuoteText bit = 0, -- Quotes the ALTER Script and executes string
@AuthorName varchar(50) = NULL -- Optional if left will output the current user.
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @myCursor CURSOR
DECLARE @ProcedureName varchar(200)
DECLARE @ProcedureId int
DECLARE @RoutineSchema varchar(128)
DECLARE @RoutineTypeName varchar(128)
DECLARE @RoutineType varchar(2)
DECLARE @myCursor2 CURSOR
DECLARE @PropertyName varchar(128)
DECLARE @PropertyValue varchar(8000)
DECLARE @CurrentDB varchar(100)
SELECT @CurrentDB = DB_Name()
DECLARE @StoredProcs TABLE
(
ProcedureName varchar(100),
ProcedureId int,
RoutineSchema varchar(128),
RoutineTypeName varchar(128),
RoutineType varchar(2)
)
DECLARE @myCursor3 CURSOR
DECLARE @ProcedureLine nvarchar(255)
DECLARE @prevProcedureLine nvarchar(280) -- Bigger than @ProcedureLine to allow for extra quotes
SET @prevProcedureLine = '' --Initialise
DECLARE @Quote AS varchar(2)
DECLARE @DoubleQuote AS varchar(2)
SET @Quote = ''''
SET @DoubleQuote = @Quote + @Quote
DECLARE @ReplaceCreateWithAlter bit
DECLARE @StoredProcsDefinitionTable TABLE
(
ProcedureLine nvarchar(255)
)
IF @AuthorName IS NULL
SET @AuthorName = RIGHT(SUser_SName(), Len(SUser_SName()) - CharIndex('\', SUser_SName()))
--END IF
INSERT INTO @StoredProcs
(
ProcedureName,
ProcedureId,
RoutineSchema,
RoutineTypeName,
RoutineType
)
SELECT O.[Name],
O.Id,
IsNull(R.ROUTINE_SCHEMA, 'dbo'),
IsNull(R.ROUTINE_TYPE, CASE
WHEN O.[TYPE] = 'TR' THEN 'TRIGGER'
ELSE NULL --(SELECT O1.[TYPE_DESC] FROM sys.objects O1 WHERE O1.[Object_Id] = O.[Id])
END),
O.[Type]
FROM SysObjects O
LEFT JOIN INFORMATION_SCHEMA.ROUTINES R
ON R.ROUTINE_NAME = O.[Name]
WHERE O.category = 0
AND O.[name] NOT LIKE 'sp\_' ESCAPE '\'
AND O.[name] LIKE @ProcName
AND IsNull(R.ROUTINE_TYPE, CASE
WHEN O.[TYPE] = 'TR' THEN 'TRIGGER'
ELSE NULL --(SELECT O1.[TYPE_DESC] FROM sys.objects O1 WHERE O1.[Object_Id] = O.[Id])
END) IS NOT NULL
ORDER BY R.ROUTINE_SCHEMA ASC,
R.ROUTINE_TYPE ASC,
O.[Type] ASC,
O.[name] ASC
SET @myCursor = CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR SELECT ProcedureName,
ProcedureId,
RoutineSchema,
RoutineTypeName,
RoutineType
FROM @StoredProcs
ORDER BY RoutineSchema ASC,
RoutineTypeName ASC,
RoutineType ASC,
ProcedureName ASC
OPEN @myCursor
FETCH NEXT
FROM @myCursor
INTO @ProcedureName,
@ProcedureId,
@RoutineSchema,
@RoutineTypeName,
@RoutineType
WHILE (@@Fetch_Status = 0)
BEGIN
IF (@IncludeSeperators = 1)
BEGIN
PRINT '-- %%START%% ' + @RoutineTypeName + '.' + @RoutineSchema + '.' + @ProcedureName
END
--END IF
IF (@IncludeAuditTrail = 1)
BEGIN
PRINT '-- **********************************************************************'
PRINT '-- $Workfi' + 'le:$'
PRINT '-- AUTHOR : ' + @AuthorName
PRINT '-- DATE CREATED : ' + Convert(varchar, GetDate(), 103)
PRINT '-- REVISION : $Revi' + 'sion:$'
PRINT '-- DESCRIPTION : Script to create if non existent then amend SQL Server'
PRINT '-- ' + Lower(@RoutineTypeName) + ' ' + @ProcedureName
PRINT '-- **********************************************************************'
PRINT '-- $Lo' + 'g:$'
PRINT '--'
PRINT '--'
IF @IncludeUseDatabase = 1
BEGIN
PRINT 'USE ' + @CurrentDB
END
--END IF
PRINT 'GO'
PRINT ''
END
--END IF
IF @RoutineType IN('TR') -- Trigger Processing just drop the trigger then create it rather the altering trigger
BEGIN
SET @ReplaceCreateWithAlter = 0 -- False
PRINT 'IF Object_Id(N'+ @Quote + @ProcedureName + @Quote + ', N' + @Quote + RTrim(@RoutineType) + @Quote + ') IS NOT NULL'
PRINT 'BEGIN'
PRINT ' DROP ' + @RoutineTypeName + ' [' + @RoutineSchema + '].[' + @ProcedureName + ']'
PRINT 'END'
END
ELSE
BEGIN
SET @ReplaceCreateWithAlter = 1 -- True
PRINT 'IF Object_Id(N'+ @Quote + @ProcedureName + @Quote + ', N' + @Quote + RTrim(@RoutineType) + @Quote + ') IS NULL'
PRINT 'BEGIN'
IF @RoutineType = 'P'
PRINT ' EXEC (''CREATE PROCEDURE [' + @RoutineSchema + '].[' + @ProcedureName + '] AS BEGIN SELECT 1 END'')'
ELSE IF @RoutineType = 'TF'
PRINT ' EXEC (''CREATE FUNCTION [' + @RoutineSchema + '].[' + @ProcedureName + '] () RETURNS @table TABLE (VALUE nchar(1)) AS BEGIN RETURN END'')'
ELSE IF @RoutineType = 'FN'
PRINT ' EXEC (''CREATE FUNCTION [' + @RoutineSchema + '].[' + @ProcedureName + '] () RETURNS varchar(1) AS BEGIN RETURN ''''X'''' END'')'
--END IF
SET @myCursor2 = CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY
FOR SELECT ep.[Name],
Cast(ep.[value] AS varchar(8000))
FROM sys.extended_properties ep
WHERE ep.major_id = @ProcedureId
AND ep.[Name] NOT LIKE 'MS/_%' ESCAPE '/'
OPEN @myCursor2
FETCH NEXT
FROM @myCursor2
INTO @PropertyName,
@PropertyValue
WHILE (@@Fetch_Status = 0)
BEGIN
PRINT ' EXEC sys.sp_addextendedproperty @name=N'+ @Quote + @PropertyName + @Quote + ', @value=N' + @Quote + @PropertyValue + @Quote + ' ,@level0type=N''SCHEMA'', @level0name=N'''
+ @RoutineSchema + @Quote + ', @level1type=N' + @Quote + @RoutineTypeName + @Quote + ', @level1name=N' + @Quote + @ProcedureName + @Quote
FETCH NEXT
FROM @myCursor2
INTO @PropertyName,
@PropertyValue
END
--END WHILE
CLOSE @myCursor2
DEALLOCATE @myCursor2
PRINT 'END'
END
--END IF
PRINT 'GO '
PRINT ''
PRINT 'GO '
INSERT INTO @StoredProcsDefinitionTable
(
ProcedureLine
)
EXEC sp_helptext @ProcedureName
SET @myCursor3 = CURSOR FAST_FORWARD
FOR
SELECT ProcedureLine
FROM @StoredProcsDefinitionTable
OPEN @myCursor3
FETCH NEXT
FROM @myCursor3
INTO @ProcedureLine
DECLARE @CreateFound bit
DECLARE @FirstLinePrinted bit
SET @CreateFound = 0 --False
SET @FirstLinePrinted = 0 --Initialise
WHILE (@@Fetch_Status = 0)
BEGIN
IF (@CreateFound = 0)
AND (PatIndex ('%CREATE%' + @RoutineTypeName + '%' + @ProcedureName + '%', @ProcedureLine) BETWEEN 1 AND 4)
BEGIN
SET @ProcedureLine = Replace(@ProcedureLine, char(9), ' ')
SET @ProcedureLine = Replace(@ProcedureLine, ' ', ' ')
SET @ProcedureLine = Replace(@ProcedureLine, ' ', ' ')
SET @CreateFound = 1
IF @QuoteText = 0
BEGIN
IF @ReplaceCreateWithAlter = 1
BEGIN
PRINT Replace(@ProcedureLine, 'CREATE ' + @RoutineTypeName, 'ALTER ' + @RoutineTypeName)
END
ELSE
BEGIN
PRINT @ProcedureLine
END
--END IF
END
ELSE
BEGIN
IF @FirstLinePrinted = 0
BEGIN
PRINT 'DECLARE @sSQL varchar(Max)'
PRINT 'SET @sSQL = ' + @Quote + Replace(@ProcedureLine, 'CREATE ' + @RoutineTypeName, 'ALTER ' + @RoutineTypeName)
SET @FirstLinePrinted = 1
END
ELSE
BEGIN
IF @ReplaceCreateWithAlter = 1
BEGIN
PRINT Replace(Replace(@ProcedureLine, 'CREATE ' + @RoutineTypeName, 'ALTER ' + @RoutineTypeName), @Quote, @DoubleQuote)
END
ELSE
BEGIN
PRINT Replace(@ProcedureLine, @Quote, @DoubleQuote)
END
--END IF
END
--END IF
END
--END IF
END
ELSE
IF @QuoteText = 0
BEGIN
PRINT @ProcedureLine
END
ELSE
BEGIN
IF @FirstLinePrinted = 0
BEGIN
PRINT 'DECLARE @sSQL varchar(Max)'
PRINT 'SET @sSQL = ''' + Replace(@ProcedureLine, @Quote, @DoubleQuote)
SET @FirstLinePrinted = 1
END
ELSE
SET @prevProcedureLine = Replace(@ProcedureLine, @Quote, @DoubleQuote)
--END IF
END
--END IF
--END IF
FETCH NEXT
FROM @myCursor3
INTO @ProcedureLine
IF @prevProcedureLine <> ''
BEGIN
IF (@@Fetch_Status <> 0) AND (@QuoteText = 1)
PRINT @prevProcedureLine + @Quote -- Last line
ELSE
PRINT @prevProcedureLine -- Last line
--END IF
SET @prevProcedureLine = '' --Initialise
END
--END IF
END
--END WHILE
DELETE @StoredProcsDefinitionTable
CLOSE @myCursor3
DEALLOCATE @myCursor3
IF (@FirstLinePrinted > 0) AND (@QuoteText = 1)
PRINT 'EXEC (@sSQL)'
PRINT 'GO '
PRINT ''
PRINT ''
IF (@IncludeSeperators = 1)
BEGIN
PRINT '-- %%END%% ' + @RoutineTypeName + '.' + @RoutineSchema + '.' + @ProcedureName
END
--END IF
FETCH NEXT
FROM @myCursor
INTO @ProcedureName,
@ProcedureId,
@RoutineSchema,
@RoutineTypeName,
@RoutineType
END
--END WHILE
CLOSE @myCursor
DEALLOCATE @myCursor
END
GO
Then run it. It will generate a script for each stored procedure in your database, just copy and paste the messages into SSMS and you have a script that will alter all the stored procedures. If you do a find and replace on the script you can change it so the code has the replacements you want. Then run it to update the sprocs.
If you then read this article it explains how you can save the script with all the SPs to a file, then run a vbscript on it to chop it into individual files, one for each stored procedure. Then you can add them to source control and have proper version control on your code.
May 1, 2020 at 3:18 pm
why don't you do what I said and install Visual Studio (SSDT) - you can then generate a VS solution/project with all objects on your database, do the global replace and reapply the changes to your db in one go.
No need for a license of VS - SSDT is free
download from here https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15
or here
you should have this in any case for all your db's
May 1, 2020 at 5:28 pm
It's not like I can just install Visual Studio on the server that is hosting the databases without discussing with higher ups. Additionally I believe this requires a coding ability outside of SQL.
I'm looking for a sql solution. Can anyone on sqlservercentral help?
--Quote me
May 1, 2020 at 5:41 pm
It's not like I can just install Visual Studio on the server that is hosting the databases without discussing with higher ups. Additionally I believe this requires a coding ability outside of SQL.
I'm looking for a sql solution. Can anyone on sqlservercentral help?
first you would not install VS on the server - it is a client application.
second - as pointed out you should have all your code on some type of source control system - so do the changes there and then deploy the changed code to your servers the same way as you would do with any other code change your company does.
third - it does not require any coding abilities - it is pure click all the way (apart from the global replace where you do need to type)
as for t-sql changes - I've pointed out some issues so if you wish to have a pure T-SQL option you can take what I mentioned in consideration and improve your code so it does what is required .
Note that you need to do the table changes prior to do the code changes or creating the proc will fail.
and if you need further help put here (following the correct way of putting code on the forums) a fully functional version of your replacement code - including the dynamic sql required to loop through your databases (which you can find some examples here on the forum or google)
The fastest way for you to do this is in SSMS Object Explorer, select the "Stored Procedures" node, then press F7. In the right-hand pane then select all the stored procedures and right-click "Script Stored Procedure\Create To\New Query Editor Window". This will script all the stored procedures into a single script.
In the script then do a find "CREATE PROCEDURE " and replace with "ALTER PROCEDURE "
Then do a find and replace to correct the error you want to, then execute the script to apply the changes.
May 3, 2020 at 5:04 am
Thank you Jonathan AC Roberts for two SQL ways. I currently have no idea what the CURSOR that you shared does, but I'm looking it over and I hope it will be a good framework that get's me going. Jonathan, did you personally write this cursor script that you shared? What does setting variable to '%' do ie. @ProcName varchar(128) = '%' ??
I like the simplicity and 'accessibility' of the SSMS directions that I marked as 'ANSWER' .
As for the other ways suggested (VS) it's outside of where I wanted to do my solutioning, but intriguing. Bookmarked for review.
--Quote me
May 4, 2020 at 3:06 am
Something simple like below might work. I've had issues with one of the tables, not sure if it was sql_modules... but it truncates the procedure in the sys view.
*Wrote on my phone, might have to covert(nvarchar(max),) for object definition.
Declare @processCode nvarchar(max)
Declare @phraseToReplace ncarchar(100)
Declare @phraseToReplace ncarchar(100)
Declare ProcessFix Cursor Local Fast_forward
For
Select replace(OBJECT_DEFINITION(object_id),@phraseToReplace,@newPhrase)
From sys.procedures
Where charindex(@phraseToReplace, OBJECT_DEFINITION(object_id))>0
Open ProcessFix
Fetch next from ProcessFix into @ProcessCode
While @@fetchstatus=0
Begin
Select @processcode=replace(@processcode,'create procedure','alter procedure')
Exec sp_executesql @processcode
Fetch next from ProcessFix into @ProcessCode
End
Close processfix
Deallocate processfix
Here's something similar I did some months ago
MCSA, Data Architect
SQL Master Data Management
https://youtube.com/elricsims-dataarchitect
9001st Normal Form
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply