Applied SQL: sys.sql_modules

Aug 222011

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!

Leave a Reply Cancel reply