Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Applied SQL: sys.objects

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

Comments

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

Leave a Comment

Please register or log in to leave a comment.