CODEWITHSUNDEEP
sql-serverreplace
Firmack Bendera
Firmack Bendera

Reputation: 35

Issue with the T-SQL Replace function

I'm currently facing a strange situation.

I'm collecting code of existing stored procedures from a query to a TMP table.

TABLE: 

##SPListAndCode
(
    Code nVarchar(MAX)
)

Query:

INSERT INTO ##SPListAndCode
    SELECT OBJECT_DEFINITION (OBJECT_ID('SPname')))

After that I am trying to replace values to get from Create query, Alter query

REPLACE(CODE, 'CREATE PROCEDURE', 'ALTER PROCEDURE')

But problem is this: REPLACE function is not replacing values.

But, when I am trying to use 

REPLACE(CODE, 'CREATE', 'ALTER')

function works as expected.

But this scenario are not acceptable for me, because inside the stored procedure there can be things like

CREATE TABLE

Example data inside "Code" column:

/****** Object:  StoredProcedure dbo.spName    Script Date: 6/20/2016 9:10:18 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE dbo.spName
AS
DECLARE  @pStartDate  date, @x int


SET @pStartDate  = (SELECT max(CT_ACTIVITY_DATE) FROM Table)

...

Thanks a lot in advance for any kind of support!

Upvotes: 0

Views: 132

Answers (2)

user6691848
user6691848

Reputation:

Your stored procedure has two spaces between CREATE and PROCEDURE, while your replace is looking for the string with a single space between the words.

Upvotes: 1

LogicalMan
LogicalMan

Reputation: 384

To gain access to the actual code contained inside of the stored procedures, you can use something like this:

SELECT 
    so.name [ObjectName], so.type, 
    OBJECT_NAME(sc.id), sc.id, sc.colid , sc.[text]
FROM 
    sys.syscomments sc
INNER JOIN 
    sys.sysobjects so ON so.id = sc.id
WHERE 
    so.type = 'P'
ORDER BY 
    sc.id, sc.colid

Note there can be multiple entries for each object, and the colid is used to order those entries.

Upvotes: 0

Related Questions