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

 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.

 FROM sys.objects o
 WHERE o.type = 'U'
 AND 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 AS SchemaName
 , 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 as TableName
 , AS ColumnName
 FROM sys.objects o
 JOIN sys.columns c
 ON o.object_id = c.object_id
 AND o.type = 'U'
 AND 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:

Leave a Reply to Jen McCown Cancel reply

Your email address will not be published.

One thought on “Applied SQL – You’ve got homework