So what are SQL modules? Simply put, SQL modules are objects in the database that are programmed in SQL. This isn't entirely accurate, as there are a few SQL programmable object types that don't fall under the 'sql module' banner. These are:

• Computed columns
• Check constraints
• Default constraints

So, effectively, that leaves us with stored procedures, replication filter procedures, the three types of functions (scalar, in-line table-valued and multi-statement table-valued) as well as both DML and DDL triggers. Also, the legacy 'standalone rule' and 'standalone default' object definitions count as SQL modules. However, the less said about them the better, frankly! The meta-data for these objects is stored in the system view [sys].[sql_modules] - so let's look at what we're offered:

So we get nearly enough to create the script for all SQL modules from this one view. The major exception being the correction of object names for sql modules that have had their name changed using sp_rename. Interestingly, if a procedure is altered using ALTER PROCEDURE, then the definition in [sys].[sql_modules] will still contain the CREATE keyword.

About system stored procedures

System stored procedures are not magic, they are just SQL modules. Their defintion can be found in [sys].[all_sql_modules], in exactly the same format as for [sys].[sql_modules]. Ok, so they are slightly magic - they often reference internal objects that you simply don't have access to as a normal SQL user. But, looking at the source of system stored procedures can be a great way to learn more about the internals of SQL server, as well as giving you the basis for creating your own customised versions.

Next time we'll look at some of the extra meta-data that is available around SQL modules, specifically triggers, stored procedures and replication filter procedures.