Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Applied SQL: sys.sql_modules

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

Comments

Posted by Jason Brimhall on 18 August 2011

Great idea on this series Jen.  These are useful exercises for new and old DBA alike.  I find myself writing queries like this all the time.

Leave a Comment

Please register or log in to leave a comment.