What command would return the text of a View / Procedure / Function / Trigger from within ISQL? I found the help article about viewing the system objects using

SELECT viewtext
FROM SYS.SYSVIEWS;

But I want to see the text of views and procedures and functions and triggers, from any user.

I do not need to output it, just view it in order to compare one to another on a different system.

Edited to include Bruce's suggestion with syntax:

call sp_helptext ('owner.object')  

All three of the answers below are answers depending on the situation.

asked 17 Dec '10, 20:12

Siger%20Matt's gravatar image

Siger Matt
3.2k496997
accept rate: 13%

edited 20 Dec '10, 20:04

Siger, usually you just would execute the stored procedure with CALL, i.e. "call sp_helptext('owner.object')".

(20 Dec '10, 15:16) Volker Barth

Got it. Adjusted.

(20 Dec '10, 20:05) Siger Matt

You will have to use different SQL queries for the different types of database objects (but could UNION them if wanted), such as

select 'Procedure/Function', proc_name, proc_defn from sys.sysprocedure
union all
select 'Trigger', trigger_name, trigger_defn from sys.systrigger
union all
select 'View', viewname, viewtext from sys.sysviews
order by 1, 2

That's just a starting point, you would usually filter out certain owners and the like (or display owner names), and would select further columns, too.

Note that for all these objects the actual SQL statement is retained in the "source" column if the option preserve_source_format is set (as default). That might be better to look at.

Note furthermore, that different tables can use triggers with the same names.

permanent link

answered 20 Dec '10, 09:46

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

permanent link

answered 18 Dec '10, 11:54

Breck%20Carter's gravatar image

Breck Carter
26.9k437609883
accept rate: 21%

That is excellent for comparing one entire db to another, but most of the time I am just checking one or two items. Is there a statement to check those individually?

(19 Dec '10, 17:03) Siger Matt

If the object's definition is small, you could use sp_helptext to display the definition and visually compare it. Once the size is large enough that visual comparison becomes difficult, unloading the definitions to files and using a diff utility is more practical. If you are only interested in whether the objects are identical or not, rather than what the differences are, then a comparison in SQL could be done.

permanent link

answered 20 Dec '10, 14:53

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

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:

×2
×1

question asked: 17 Dec '10, 20:12

question was seen: 878 times

last updated: 20 Dec '10, 20:04