Applied SQL – You’ve got homework

Jen McCown (blog | twitter) is giving us homework. When I was a kid I didn’t like homework. It was always so boring. Well, except for the time I had to do a diorama on dinosaurs. Now that was fun. Kids don’t do dioramas anymore do they? Pity.

Anywho, Jen has started a series on her SQL Awesomesauce blog called Applied SQL. The point of the exercise being that, while we can read all we want about SQL, the concepts don’t truly sink in until we apply them. Practice. Practice. Practice. I know that certainly holds true for me, so I plan on participating. And btw, now that I’m all grown up, I love homework! So head on over to Jen’s blog and get crackin’ on assignment 1.

Here’s mine:

-- Level 1
-- Assignment 1: Get a list of all views

SELECT o.name
 FROM sys.objects o
 WHERE o.type = 'V'

-- Level 1
-- Assignment 2: Get a list of all tables with the word “product” in the name.

SELECT o.name
 FROM sys.objects o
 WHERE o.type = 'U'
 AND o.name LIKE '%product%'

-- Level 2
-- Assignment 1: 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 s.name AS SchemaName
 , o.name AS TableName
 FROM sys.schemas s
 JOIN sys.objects o
 ON s.schema_id = o.schema_id
 AND o.type = 'U'

-- Level 2
-- Assignment 2: 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.name as TableName
 , c.name AS ColumnName
 FROM sys.objects o
 JOIN sys.columns c
 ON o.object_id = c.object_id
 AND o.type = 'U'
 AND c.name LIKE '%address%'

P.S. There used to be a picture of that diorama floating around in the house. I’ll have to see if I can find it.

Also recommended: