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