SYSPROCEDURE.proc_defn vs. SYSPROCEDURE.source

[Baron]

What is the difference between the columns source & proc_defn in table SYSPROCEDURE?

Why is sometimes the column source (NULL) and why it sometimes differ from the column proc_defn?

For me it looks like the source contains the exact script how the procedure is created, and the proc_defn contains the optimized definition of the procedure, but is there a reason to maintain those two columns?

[VolkerBarth]

The "source" column is always filled (with the original CREATE ... statement as taken from the last CREATE/ALTER call) unless the "preserve_source_format" option is set to Off. This also holds for other "code-based" objects like events, views and triggers.

I'd recommend to leave it set to its default "On" value because the optimized/parsed version in the "proc_defn" may change significantly between database versions, so it might make it difficult to compare code changes. (That being said, I'd also highly recommend to manage all relevant database scripts via your source version control tool of choice...)

FWIW, there are several forum questions discussing the pros and cons... - or just the pros. 🙂

[Baron]

Thanks,

But I understand that the optimized (nice formatted) source is saved in the column proc_defn, and the column source contains the source as it was created. Is it on this page reversed? Or I misunderstood it?

[VolkerBarth]

optimized (nice formatted)

No, "optimized" means "parsed" here, as used by the system, whereas "source" means "code as is". IMHO, the docs are correct.

[Breck_Carter]

Folks (like me) who read too fast and sometimes miss the whole context might get the wrong idea from Volker's first few words...

The "source" column is always filled ...

For low-retention readers (like me) this might be better:

The proc_defn column is always filled, although the contents will be unreadable if ALTER PROCEDURE SET HIDDEN has been run. The source column is optional; it will be filled if preserve_source_format was on when the procedure was last created or replaced, and ALTER PROCEDURE SET HIDDEN has been not subsequently been run, otherwise it will be null.

I agree very strongly with Volker that you should not rely on SYSPROCEDURE for storing your source code. You should store your procedure SQL code in text files.

[Breck_Carter]

"parsed"

In this context, "parsed" is French for "mangled" 🙂

[VolkerBarth]

Folks (like me) who read too fast

Folks (like me) who write too fast typically ignore details like SET HIDDEN altogether... - thanks for pointing that out 🙂