SYSPROCEDURE.proc_defn vs. SYSPROCEDURE.source

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?

stored-procedure system-tables

asked 24 Jun '21, 02:16

Baron
2.1k●137●150●177
accept rate: 48%

flat view

One Answer:

active answers oldest answers newest answers popular answers

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 '21, 02:55

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 24 Jun '21, 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 '21, 05:14) Baron

Replies hidden

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 '21, 07:53) Volker Barth

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 '21, 09:39) Breck Carter

Replies hidden

"parsed"

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

(24 Jun '21, 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 '21, 10:00) Volker Barth

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

stored-procedure ×125
system-tables ×32

question asked: 24 Jun '21, 02:16

question was seen: 1,080 times

last updated: 24 Jun '21, 10:00

SAP SQL Anywhere

SYSPROCEDURE.proc_defn vs. SYSPROCEDURE.source

Follow this question

Related questions