Monday, January 28, 2013

system_sql_modules and all_sql modules views

Previously I'd blogged on the advantages of using sys.sql_modules view here

There are two more related views available from SQL 2005 onwards. They're sys.system_sql_modules and sys.all_sql_modules. So how are they different from sys.sql_modules?

The difference is as follows

sys.sql_modules - Will have a record for each user created modules in the database like procedure, view, trigger etc.
sys.system_sql_modules - Will have a record for each system modules in the database
sys.all_sql_modules - Will include the results of both the above views together i.e. it will have system as well as user modules in the current database listed
The advantages I see with these views is that they contain entire object definition included within definition column which is of BLOB type which we can query out using a simple select statement.