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
USE AdventureWorks
GO
— Level 1-1
SELECT definition
FROM sys.sql_modules
WHERE LOWER(definition) LIKE ‘%CONVERT%’
— Level 1-2
SELECT definition
FROM sys.sql_modules
WHERE LOWER(definition) LIKE ‘%NOLOCK%’
— Level 2-1
SELECT name, definition
FROM sys.sql_modules smod
INNER JOIN sys.objects obj ON smod.object_id = obj.object_id
WHERE obj.type = ‘FN’
— Level 2-2
SELECT sch.name AS [Schema], obj.name AS [View], smod.definition
FROM sys.sql_modules smod
INNER JOIN sys.objects obj ON smod.object_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE obj.type = ‘V’
— Level 3-1
SELECT DISTINCT obj.type
FROM sys.objects obj
LEFT JOIN sys.sql_modules smod ON obj.object_id = smod.object_id
WHERE smod.object_id IS NULL
And… I just realised I should’ve been UPPER()ing in my first two solutions. Ah well.
Hi Jen.
Here are my answers to your homework:
http://pastebin.com/JYzA1x8Y
I would appreciate if you had little time to tell me if it is correct.
Thank you very much.
Jan
— Level 1
SELECT sqlmod.[definition]
FROM sys.sql_modules sqlmod
WHERE sqlmod.[definition] LIKE ‘%CONVERT%’
SELECT sqlmod.[definition]
FROM sys.sql_modules sqlmod
WHERE sqlmod.[definition] LIKE ‘%NOLOCK%’
— Level 2
SELECT obj.name, sqlmod.[definition]
FROM sys.sql_modules sqlmod
INNER JOIN sys.objects obj ON sqlmod.object_id = obj.object_id
WHERE obj.type = ‘FN’
SELECT sch.name, obj.name, sqlmod.[definition]
FROM sys.sql_modules sqlmod
INNER JOIN sys.objects obj ON sqlmod.object_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE obj.type = ‘V’
— Level 3
SELECT DISTINCT obj.type_desc
FROM sys.objects obj
WHERE obj.object_id NOT IN
(SELECT OBJECT_ID
FROM sys.sql_modules)
/*———————————————————————————————
Level 1 – Using sys.sql_modules, write a query for each of the following:
1. Find all object definitions that contain the word “CONVERT.”
2. Find all object definitions that contain a “NOLOCK” query hint.
———————————————————————————————*/
USE AdventureWorks2008
GO
SELECT *
FROM sys.sql_modules
WHERE [definition] LIKE ‘%CONVERT%’
SELECT *
FROM sys.sql_modules
WHERE [definition] LIKE ‘%NOLOCK%’
/*———————————————————————————————
Level 2 – Using sys.sql_modules, write a query for each of the following:
1. 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.
2. Get the schema, name and the definition of each view in the database. You will
need to join to one or more catalog views.
———————————————————————————————*/
SELECT so.[name],
so.[type_desc],
sm.[definition]
FROM sys.objects so
INNER JOIN sys.sql_modules sm
ON sm.[object_id] = so.[object_id]
WHERE so.[type_desc] = ‘SQL_SCALAR_FUNCTION’
SELECT ss.[name],
so.[name],
so.[type_desc],
sm.[definition]
FROM sys.objects so
INNER JOIN sys.schemas ss
ON so.[schema_id] = ss.[schema_id]
INNER JOIN sys.sql_modules sm
ON so.[object_id] = sm.[object_id]
WHERE so.[type_desc] = ‘VIEW’
/*———————————————————————————————
Level 3 – Using sys.sql_modules, write a query for each of the following:
1. 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.
———————————————————————————————*/
SELECT DISTINCT so.[type_desc]
FROM sys.objects so
LEFT JOIN sys.sql_modules sm
ON so.[object_id] = sm.[object_id]
WHERE sm.[object_id] IS NULL
USE AdventureWorks;
GO
— Level 1
— Assignment 1
SELECT sm.object_id
, sm.definition
FROM sys.sql_modules sm
WHERE sm.definition LIKE ‘%CONVERT%’
— Level 1
— Assignment 2
SELECT sm.object_id
, sm.definition
FROM sys.sql_modules sm
WHERE sm.definition LIKE ‘%NOLOCK%’
— Level 2
— Assignment 1
SELECT o.name
, sm.definition
FROM sys.objects o
JOIN sys.sql_modules sm
ON o.object_id = sm.object_id
AND o.type = ‘FN’
— Level 2
— Assignment 2
SELECT s.name AS SchemaName
, o.name as ViewName
, sm.definition AS ViewDefinition
FROM sys.schemas s
JOIN sys.objects o
ON s.schema_id = o.schema_id
JOIN sys.sql_modules sm
ON o.object_id = sm.object_id
AND o.type = ‘V’
— Level 3
— Assignment 1
SELECT DISTINCT o.type
, o.type_desc
FROM sys.objects o
WHERE NOT EXISTS
(SELECT 1 FROM sys.sql_modules sm WHERE sm.object_id = o.object_id)
USE AdventureWorks
GO
/* •Find all object definitions that contain the word “CONVERT”. */
select * from sys.sql_modules
where definition like ‘%CONVERT%’
go
/* •Find all object definitions that contain a NOLOCK query hint. */
select * from sys.sql_modules
where definition like ‘%NOLOCK%’
go
/* • 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 ob.name, mo.definition
from sys.sql_modules mo inner join sys.objects ob on mo.object_id = ob.object_id
where ob.type = ‘FN’
go
/* •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 sc.name, ob.name, mo.definition
from sys.sql_modules mo inner join sys.objects ob on mo.object_id = ob.object_id
inner join sys.schemas sc on sc.schema_id = ob.schema_id
where ob.type = ‘V’
go
/* •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.*/
select ob.type
from sys.objects ob
where ob.object_id not in (
select distinct mo.object_id
from sys.sql_modules mo)
group by ob.type
GO
Select
distinct so.[object_id], so.[name], so.[type]
from
sys.objects so
inner join
sys.sql_modules sm
on so.[object_id]=sm.[object_id]
where sm.[definition] like ‘%convert%’
order by so.[type],so.[name]
Select
distinct so.[object_id], so.[name], so.[type]
from
sys.objects so
inner join
sys.sql_modules sm
on so.[object_id]=sm.[object_id]
where sm.[definition] like ‘%NOLOCK%’
order by so.[type],so.[name]
Select
so.[object_id], so.[name], so.[type], sm.[definition]
from
sys.objects so
inner join
sys.sql_modules sm
on so.[object_id]=sm.[object_id]
where so.[type] = ‘FN’
order by so.[type],so.[name]
Select
so.[object_id], ss.[name], so.[name], so.[type], sm.[definition]
from
sys.objects so
inner join
sys.sql_modules sm
on so.[object_id]=sm.[object_id]
inner join
sys.schemas ss
on so.[schema_id] = ss.[schema_id]
where so.[type] = ‘V’
order by so.[type],so.[name]
Select
distinct
so.[type], so.type_desc
from
sys.objects so
left outer join
sys.sql_modules sm
on so.[object_id]=sm.[object_id]
inner join
sys.schemas ss
on so.[schema_id] = ss.[schema_id]
where sm.[object_id] is null
My solutions for lesson two are found here. Solution Two
Thanks for doing this, I’m enjoying the “Homework” so far.
USE AdventureWorks;
SELECT o.[name] AS ObjectName, o.type_desc AS [Type], m.[definition]
FROM sys.objects o JOIN sys.sql_modules m ON o.[object_id] = m.[object_id]
WHERE m.[definition] LIKE ‘%convert%’
ORDER BY ObjectName;
SELECT o.[name] AS ObjectName, o.type_desc AS [Type], m.[definition]
FROM sys.objects o JOIN sys.sql_modules m ON o.[object_id] = m.[object_id]
WHERE m.[definition] LIKE ‘%nolock%’
ORDER BY ObjectName;
SELECT o.[name] AS ObjectName, m.[definition], is_ms_shipped
FROM sys.objects o JOIN sys.sql_modules m ON o.[object_id] = m.[object_id]
WHERE o.[type] = ‘FN’
ORDER BY ObjectName;
SELECT o.[name] AS ObjectName, m.[definition], s.[name] AS SchemaName
FROM sys.objects o
JOIN sys.sql_modules m ON o.[object_id] = m.[object_id]
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.[type] = ‘V’
ORDER BY SchemaName;
DECLARE @t TABLE ([type] char(2) COLLATE Latin1_General_CI_AS_KS_WS )
–types having an entry in sys.sql_modules according BOL. I did not add ‘D’
INSERT INTO @t
SELECT ‘P’
UNION
SELECT ‘RF’
UNION
SELECT ‘V’
UNION
SELECT ‘TR’
UNION
SELECT ‘FN’
UNION
SELECT ‘IF’
UNION
SELECT ‘TF’
UNION
SELECT ‘R’
SELECT DISTINCT [type] FROM sys.objects
EXCEPT
SELECT * FROM @t
I don’t think that my trackback worked.
here are my answers: http://nelsonsweb.net/2011/08/applied-sql-sys-sql_modules/
Pingback: Applied SQL: sys.sql_modules »
Here are my solutions (of the many possible solutions!) to the assignments:
–Level 1 Assignments: Using sys.sql_modules, write a query for each of the following:
–Find all object definitions that contain the word “CONVERT”.
SELECT DEFINITION
FROM sys.sql_modules
WHERE definition LIKE ‘%convert%’;
— Notes:
— Find all object definitions that contain a NOLOCK query hint.
SELECT DEFINITION
FROM sys.sql_modules
WHERE definition LIKE ‘%nolock%’;
— 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 system table; lose points for using OBJECT_NAME.)
SELECT O.NAME, M.DEFINITION
FROM sys.sql_modules M
INNER JOIN sys.objects O ON O.object_id = M.object_id
WHERE O.type = ‘FN’;
— Get the schema, name, and definition of each view in the database.
— (You will need to join to one or more system table; lose points for using SCHEMA_NAME or OBJECT_NAME.)
SELECT S.name schema_name, O.NAME, M.DEFINITION
FROM sys.sql_modules M
INNER JOIN sys.objects O ON O.object_id = M.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id
WHERE O.type = ‘V’;
— Level 3 assignment: Discover what TYPES of objects in this database do not have an entry in sys.sql_modules.
SELECT DISTINCT O.TYPE FROM sys.objects AS O
WHERE O.TYPE NOT IN (
SELECT O.type
FROM sys.sql_modules M
INNER JOIN sys.objects O ON M.Object_id = O.object_id
)
— Note: Brandon Franklin’s solution to 3-1 above is another (and one might say more elegant) workable solution to this assignment.
Pingback: Jay Bonk » Applied SQL: sys.sql_modules » Jay Bonk
Jen –
Awesome series. I posted my answers here
Jay