June 19, 2008 at 1:22 pm
the lead programmer slammed in a set of new database updates
by scripting the entire set of stored procedures and functions
in a staging database and globally changing CREATE to ALTER
only thing, I had dutifully made Create Date's and now they're all ALTER Date - arrgh!
how do I change them back?
I ran a script and found that about one third of the objects have ALTER Date in them
I can script these and replace CREATE PROCEDURE to ALTER PROCEDURE
and of course ALTER Date back to Create Date
is there a better way?
June 19, 2008 at 1:24 pm
That's the only way I know of.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 1:26 pm
June 19, 2008 at 1:37 pm
Something like this might work:
declare Cur cursor local fast_forward for
select definition
from sys.sql_modules
where definition like '%alterdate%'
declare @SQL varchar(max)
open cur
fetch next from cur
into @SQL
select @SQL =
replace(
replace(@SQL, 'alterdate', 'createdate'),
'create proc', 'alter proc')
while @@fetch_status = 0
begin
print (@SQL)
print 'GO'
fetch next from cur
into @SQL
select @SQL =
replace(
replace(@SQL, 'alterdate', 'createdate'),
'create proc', 'alter proc')
end
I haven't tested this, I just slapped it together. Don't run it in a production database till you've tested the heck out of it!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 1:41 pm
July 9, 2008 at 11:54 am
Gary is it? (or Grant?)
Thanks!
I had to make a fix 'CREATE PROC', 'ALTER PROC'
and there actually was a stored procedure so knarly it was more than 8000 characters!
also, I ran it first as NVARCHAR(4000) for @SQL
and sp_execute @SQL
then ran it again with VARCHAR(8000) removing the automatic execute
then cut and pasted the SQL PRINTed into a query window
Marianne
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply