Applied SQL Homework — Lesson 1

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 1 from the AdventureWorks DB for SQL Server 2008 R1.


USE AdventureWorks
GO

— LEVEL ONE – Part One
— GET all Views From sys.Objects

SELECT name AS ObjectName,
    object_id AS ObjectId
FROM sys.objects
WHERE type = 'V'

GO

— LEVEL ONE – Part Two
— GET ALL Tables with a name like ‘Product’

SELECT O.object_id AS ObjectId,
    O.name AS ObjectName
FROM sys.objects O
WHERE o.name LIKE '%Product%'
AND o.[type] IN ( 'S','U' )

— LEVEL TWO — Part One
— Get a list of all tables and their schema names.
— (You will need to join to another system table; lose points for using SCHEMA_NAME.)

SELECT O.object_id AS ObjectId,
    S.name AS SChemaName,
    O.name AS TableName,
    S.name + '.' + O.name AS FQN
FROM sys.objects O
  INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
WHERE O.[type] in ('U','S')

— LEVEL TWO – Part Two
— Get a list of any tables and column name, where the column name contains the word “address”.
— (You will need to join to yet another system table.)

SELECT O.object_id AS ObjectId,
    S.name AS SChemaName,
    O.name AS TableName,
    C.name AS ColumnName,
    S.name + '.' + O.name + '.' + C.name AS FQN
FROM sys.objects O
  INNER JOIN sys.schemas S ON O.schema_id = S.schema_id
  INNER JOIN sys.columns C ON O.object_id = C.object_id
WHERE O.[type] in ('U','S')
    AND C.name LIKE '%Address%'

Screen Capture of Results:

Advertisements

1 Comment

Filed under Sql Server (Applied Skills)

One response to “Applied SQL Homework — Lesson 1

  1. Jen

    Nicely done! I hadn’t intended to include system tables, but I like that you did what the assignment said. Way to follow requirements…

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