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
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:
All three of the answers below are answers depending on the situation. asked 17 Dec '10, 20:12 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
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. answered 20 Dec '10, 09:46 Volker Barth |
answered 18 Dec '10, 11:54 Breck Carter 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? |
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. answered 20 Dec '10, 14:53 Bruce Hay |
Siger, usually you just would execute the stored procedure with CALL, i.e. "call sp_helptext('owner.object')".
Got it. Adjusted.