Jen McCown from midnightdba.com started a useful (to me anyways) set of homework assignments called Applied SQL. Each post has a homework assignment for us. This first one has been useful to me in order to make myself read Books Online and understand where information comes from, rather than just running a script when I want information to get information out of the black box.
Assignment 2 is looking at the sys.sql_modules system table.
1) Find all object definitions that contain the word “CONVERT”.
SELECT * FROM sys.sql_modules WHERE definition LIKE '%convert%'
2) Find all object definitions that contain a NOLOCK query hint.
SELECT * FROM sys.sql_modules WHERE definition LIKE '%nolock%'
3) Get a list of names and definitions for all SQL scalar functions in the database. (You will need to join to one or more catalog views; lose points for using OBJECT_NAME.)
SELECT sys.objects.name, sys.sql_modules.definition FROM sys.sql_modules INNER JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id WHERE sys.objects.TYPE = 'FN'
4) Get the schema, name, and definition of each view in the database. (You will need to join to one or more catalog views; lose points for using SCHEMA_NAME or OBJECT_NAME.)
SELECT sys.schemas.name AS SchemaName, sys.objects.name AS ViewName, sys.sql_modules.definition FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id INNER JOIN sys.sql_modules ON sys.objects.object_id = sys.sql_modules.object_id WHERE TYPE = 'v' ORDER BY sys.schemas.name, sys.objects.name
5) sys.sql_modules only contains entries for certain types of objects. Use this catalog view (and any others necessary) to find what TYPES of objects in this database do not have entries in sys.sql_modules. For example, if object types X, Y, and Z exist in this database, but do not have entries in sys.sql_modules, then they should be in the resultset.
I cheated on this one…I re-read BOL for sys.sql_modules. The second sentence tells us that the object types included in sys.sql_modules are: P, RF, V, TR, FN, IF, TF, and R. No extra effort required to write a query!