Although a little quirky, I think the proc should work fine - it seems to me that you've just missed the vendor restriction near the end...

SELECT File_Sequence_No FROM ZZ.T_ Hdr...

Interesting - thanks for the info - I think I'll have to add it to my 'topics to read about' list 😀

No, it's just a test that covers a few edge cases, and breaks every code sample you've posted.

If you've never seen something...

Seeing as I don't have VB installed - would you just run it through the proc definition that I tested my solution with?

I.e. run this code

Dim SQL as String

SQL =...

You want to use a windowed aggregate for that.

Check out BOL for information on the OVER clause, paying particular attention to the partition by bit. There's some pretty detailed examples...

Sorry to be the bearer of bad news, but you can't do that easily at all. Not generically for any stored procedure, anyhow.

SysDepends / sys.sql_dependencies / sys.sql_expression_dependencies all just show...

I have also been toying with a regex based way to solve this problem - and, using the regex \/\*.*CREATE.*\*\/|--.*CREATE|(CREATE) in case insensitive mode then you could look for the...

I don't think I've made...

Jeff Moden (9/26/2009)

---

We never experienced such a thing... not even when the source or target when down for one reason or another. Maybe we just got lucky but it...

Have you ever had any issues with that? Because we had an issue once where it...

I'm quite aware how replace works 🙂

If you must go down the string replace route, then at least use a regex to match an undefined number of spaces, rather than...

In that case, you've really got three options:

1) (easiest) Just replace 'CREATE PROC' with 'ALTER PROC' in the resulting string, and hope that it doesn't come across any procedures that...

Is there a particular reason you want to use ALTER as opposed to DROP then CREATE?

Do you have permissions you'd like to maintain?

As I said, changing CREATE statements to ALTER...

Sorry - I got completely the wrong end of the stick there! 😀

How would that handle a procedure which created another procedure dynamically?

e.g.

CREATE PROCEDURE stproc_CreateMyProc

AS

EXEC ('CREATE PROCEDURE stproc_MyProc as SELECT ''hello'' ')