Bulk replace input parameter in SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Bulk replace input parameter in SP

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2014-03-26 : 17:09:52

Hi All,

I made a mistake and made a ton of SP with the wrong parameter type

@site as nvarchar(60)

and should be

@site as varchar(50)

IS there a Query i can write to do this?
Are there any tools to do this kind of stuff?

Ahhh also got a list op SP i want to Add 'WITH RECOMPILE'

Thanks a lot

mike13
Posting Yak Master

219 Posts

Posted - 2014-03-28 : 09:20:38

Found this that solved my problem:

SELECT
b.Name AS [ObjectName],
CASE WHEN b.type ='p' THEN 'Stored Procedure'
WHEN b.type ='v' THEN 'View'
ELSE b.TYPE
END AS [ObjectType]
,a.definition AS [Definition]
,Replace ((REPLACE(definition,'OLD Value','New Value')),'Create','ALTER') AS [ModifiedDefinition]
FROM sys.sql_modules a
JOIN
( select type, name,object_id
from sys.objects
where type in (
'p' -- procedures
,'v'--views
)
and is_ms_shipped = 0
)b
ON a.object_id=b.object_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources