Warning: Use of undefined constant ’WP_POST_REVISIONS’ - assumed '’WP_POST_REVISIONS’' (this will throw an Error in a future version of PHP) in /homepages/10/d210128362/htdocs/wp-config.php on line 109

Warning: Cannot modify header information - headers already sent by (output started at /homepages/10/d210128362/htdocs/wp-config.php:109) in /homepages/10/d210128362/htdocs/wordpress/wp-includes/pluggable.php on line 1210
Applied SQL: sys.objects »
Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /homepages/10/d210128362/htdocs/wordpress/wp-content/themes/suffusion/library/suffusion-walkers.php on line 39

Warning: count(): Parameter must be an array or an object that implements Countable in /homepages/10/d210128362/htdocs/wordpress/wp-includes/post-template.php on line 284
Aug 182011
 

System tables have lots of interesting and useful information stored on all facets of SQL server.  Generally speaking, whenever I find a script that shows useful information, I store that script away for future use.  I then run the script whenever I need the information without really thinking about it.  Jen McCown from midnightdba.com started a useful (to me anyways) set of homework assignments called Applied SQL.  Each post has a homework assignment for us.  This first one has been useful to me in order to make myself read Books Online and understand where information comes from, rather than just running a script when I want information to get information out of the black box.

Assignment 1 is looking at the sys.objects system table.  Below is my homework, maybe I’ll get some bonus points for sorting my results too!

.

Assignment 1: Get a list of all views

SELECT *
FROM   sys.objects
WHERE  TYPE = 'v'
ORDER BY Name

.

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

SELECT *
FROM   sys.objects
WHERE  TYPE = 'U'
 AND name LIKE '%product%'
ORDER  BY Name

.

Assignment 3: 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 sys.objects.name AS TableName,
 sys.schemas.name AS SchemaName
FROM   sys.objects
 INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE  sys.objects.TYPE = 'U'
ORDER  BY sys.objects.name

.

Assignment 4: 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 sys.objects.name AS TableName,
       sys.columns.name AS ColumnName
FROM   sys.objects
       INNER JOIN sys.columns ON sys.objects.object_id = sys.columns.object_id
WHERE  sys.objects.TYPE = 'U'
       AND sys.columns.name LIKE '%address%'
ORDER  BY sys.objects.name,
          sys.columns.name

  2 Responses to “Applied SQL: sys.objects”

  1. I hereby award you bonus points for ORDER BY. 🙂

Leave a Reply to Matt Cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)