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?

asked 24 Jun, 02:16

Baron's gravatar image

Baron
1.4k81103127
accept rate: 47%


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. :)

permanent link

answered 24 Jun, 02:55

Volker%20Barth's gravatar image

Volker Barth
37.6k346513778
accept rate: 34%

edited 24 Jun, 03:23

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?

(24 Jun, 05:14) Baron
Replies hidden
2

optimized (nice formatted)

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

(24 Jun, 07:53) Volker Barth
1

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.

(24 Jun, 09:39) Breck Carter
Replies hidden
1

"parsed"

In this context, "parsed" is French for "mangled" :)

(24 Jun, 09:49) Breck Carter

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 :)

(24 Jun, 10:00) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×119
×32

question asked: 24 Jun, 02:16

question was seen: 119 times

last updated: 24 Jun, 10:00