Subscribe to this blog
Briefcase
Print
I introduce this whole concept in Applied SQL: You Have Homework. Find all assignments on the Applied SQL page.
Prerequisites: basic T-SQL (including JOINs and LIKE), ability to search BOL
Reading: SQL Server Books Online sys.objects article.
Level 1 Assignments: Using sys.objects, write a query for each of the following:
- Get a list of all views.
- Get a list of all tables with the word “product” in the name.
Level 2 Assignment: Using sys.objects, write a query for each of the following:
- 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.)
- 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.)
Bonus points: Post your scripts (or a link to them) in the comments below, just to show off that you did it.
Happy learning,
Jen McCown
www.MidnightDBA.com/Jen
Posted by joseph4tw on 29 August 2011
This is awesome. Thanks for posting these assignments. I really think it adds value to my knowledge of SQL Server.
Not sure if this is correct, but here it goes (please let me know what I did incorrectly):
/*
joseph4tw
2011-08-29
SQL Awesomesauce
Homework 1 - Level 1
*/
-- Get a list of all views
USE AdventureWorks;
GO
SELECT
o.name AS Name
FROM
sys.objects o
WHERE
o.type_desc = 'VIEW'
ORDER BY
o.name
-- Get a list of all tables with
-- the word "product" in the name
USE AdventureWorks;
GO
SELECT
o.name AS Name
, o.type_desc AS 'Type Description'
FROM
sys.objects o
WHERE
o.type_desc = 'USER_TABLE'
AND o.name LIKE '%product%'
ORDER BY
o.name
/*
joseph4tw
2011-08-29
SQL Awesomesauce
Homework 1 - Level 2
*/
-- Get a list of all tables
-- and their schema names
USE AdventureWorks;
GO
SELECT
o.name AS Name
, s.name AS 'Schema Name'
, o.type_desc AS 'Type Description'
FROM
sys.objects o
INNER JOIN sys.schemas s ON s.[schema_id] = o.[schema_id]
WHERE
o.type_desc LIKE '%table%'
ORDER BY
o.name
-- Get a list of any tables and column name,
-- where the column name contains the word “address”.
USE AdventureWorks;
GO
SELECT
o.name AS 'Object Name'
, c.name AS 'Column Name'
, o.type_desc AS 'Type Description'
FROM
sys.objects o
INNER JOIN sys.columns c ON c.[object_id] = o.[object_id]
WHERE
o.type_desc LIKE '%table%'
AND c.name LIKE '%address%'
ORDER BY
o.name, c.name