Applied SQL: sys.objects

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