I introduce this whole concept in Applied SQL: You Have Homework. Find all assignments on the Applied SQL page.
Prerequisites: basic T-SQL (including JOINs and LIKE), basic knowledge of catalog views
Reading: SQL Server Books Online sys.sql_modules article.
Level 1 Assignments: Using sys.sql_modules, write a query for each of the following:
- Find all object definitions that contain the word “CONVERT”.
- Find all object definitions that contain a NOLOCK query hint.
Level 2 Assignment: Using sys.sql_modules, write a query for each of the following:
- 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.)
- 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.)
Level 3 Assignment:
- 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.
Bonus points: Post your scripts (or a link to them) in the comments below, just to show off that you did it.
Happy learning,
Jen McCown
www.MidnightDBA.com/Jen
