Applied SQL Homework — Lesson 2

I’ve been following along with MidnightDBA’s (Twitter) Applied SQL, and had some down time today, so thought I’d knock out the assignments.   Here are my solutions for Lesson 2 from the AdventureWorks DB for SQL Server 2008 R1.

LESSON TWO


USE AdventureWorks;

LEVEL ONE – PART ONE

Find all object definitions that contain the word “CONVERT”.


GO
SELECT O.name AS ObjectName
, S.name
, S.name + '.' + O.name AS FQN
FROM sys.sql_modules M
INNER JOIN sys.objects O ON M.object_id = O.object_id
INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE M.definition LIKE '%CONVERT%'

LEVEL ONE – PART TWO

Find all objects using the NOLOCK Query Hint

Used the WITH Syntax to only show items where WITH(NOLOCK as opposed to Showing all items containing NOLOCK text. Using only NOLOCK could return Items where comments were stating NOT to use NOLOCK query hint.


GO
SELECT O.name AS ObjectName
, S.name
, S.name + '.' + O.name AS FQN
FROM sys.sql_modules M
INNER JOIN sys.objects O ON M.object_id = O.object_id
INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE M.definition LIKE '%WITH(NOLOCK%'

LEVEL TWO – PART ONE

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.)


GO
SELECT O.name AS ObjectName,
OBJECT_DEFINITION(o.object_id) AS ObjectDefinitionViaFunction,
M.definition AS ObjectDefinitionViaModulesTale
FROM sys.objects O
INNER JOIN sys.sql_modules M ON O.object_id = M.object_id
WHERE TYPE = 'FN'

LEVEL TWO – PART TWO

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.)

I Cheated here — Used the OBJECT_DEFINITION function, as well as linking to the system view to objtain the definition. Normally I would just use the function since its there for me to use.


GO
SELECT O.name AS ObjectName,
S.name AS SchemaName,
S.name + '.' + O.name AS FQN,
OBJECT_DEFINITION(o.object_id) AS ObjectDefinitionViaFunction,
M.definition AS ObjectDefinitionViaModulesTale
FROM sys.objects O
INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
INNER JOIN sys.sql_modules M ON O.object_id = M.object_id
WHERE TYPE = 'V'
GO

LEVEL THREE – PART ONE

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 O.type,
O.type_desc
FROM sys.objects O
LEFT JOIN sys.sql_modules M ON O.object_id = M.object_id
WHERE M.object_id IS NULL

Advertisements

Leave a comment

Filed under Sql Server (Applied Skills)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s