How to view the text of a View / Procedure / Function / Trigger in ISQL

[former_SQLA_member1694868]

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.

[VolkerBarth]

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

[former_SQLA_member1694868]

Got it. Adjusted.

[VolkerBarth · Accepted solution]

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.

[Former Member]

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.

[Breck_Carter]

See Comparing Database Schemas, Improved Edition.

[former_SQLA_member1694868]

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?