Part 1 - Your first queries

SQL Server has a lot of different system tables, one of which is called sys.objects. sys.objects has one row for every object in the current database, and numerous columns for different properties of this object. For instance we have the object_id which is an id number which is unique within the database, name which is the name of the objects and type which tells what kind of object it is. To list all the content of the table, we may issue the following query:

select
  *
from 
  sys.objects

Of course, it is very simple to return all columns and all rows from a table, but most likely that also means that we return more data than we really need. For instance, we might only want the object names and their type. We can achieve this by replace the asterisk with a comma separated list of column names. In the query below the column names have been placed in square brackets to tell SQL Server that name and type is column names instead of reserved words. We can always place names in square brackets if we want to. There only a few cases where we have to, like when a name contains a special character like space or when the name is among a group of reserved words.

select
  [name],
  [type]
from
  sys.objects

Still we get all the rows, but at least we only get those columns that we are interrested in. We can return virtually any number of columns, and in any order. The columns will be returned in the order specified in the select list. So, if we would like the columns to come in the opposite order, we would just specify type,name instead of name,type.

As you might have discovered, sys.objects does not only contain information about user objects, but system objects too. System tables have their own type, S. So, we might want to query SQL Server for the names of all system tables:

select
  [name]
from
  sys.objects
where
  type = 'S'

The magical word here is where. We want all rows returned that match the condition in the where section of the query. So, we query SQL Server for all objects that have a type of S, that is a system table. Note that the S is placed in single quotes to tell SQL Server that this is a character string. If we were comparing numbers they would not be placed in single quotes.

We might want to expand this query to include all types of tables, and there are three of them. We have User tables (U), system tables (S) and internal tables (IT). Of course, we could run a query for each of these types, but it would not be very efficient. So, we try to do it all in one go instead. The previous example showed how to create a filter (that is a where-statement) with a single comparison which should be satisfied. Now we're going to create a filter with three comparisons, where (at least) one of the should be satisfied. The syntax for each comparison is unchanged, and we list them all with the word or between.

select
  [name]
from
  sys.objects
where
  type = 'U' or 
  type = 'S' or
  type = 'IT'

As we've seen with the operator or all rows are return that match at least one of the comparisons. Similarily we could have used the operator and to only return the rows that match all of the comparisons. Since we checked for different values in the same column it would have been a contradiction to use the and operator in the previous example.

It is not too much hassle to list three different comparisons, but as the number of comparisons increase so does or desire to write it in a simpler way. As long as you should return a row if a value is in a list of known values, you can use the in operator. For instance, we could simply query SQL Server for all objects which could as a "table source" in a query. In addition to the previous three we then have views (V), inline table valued functions (IF), table valued functions (TF) and CLR table valued functions (FT). With a small change of our query we now have:

select
  [name]
from
  sys.objects
where
  type in ('U','S','IT','V','IF','TF','FT')

This is clearly simpler than a ton of comparisons with or between. If we wanted all objects except those in this list we could have written not in instead of in.

Exercises

  1. There is a system view sys.databases that contains a row for every database in your SQL Server instance. Write a query that list the name of your databases.
  2. In addition to equality (=) a comparison can contain other operators including less than (<), greater than (>), less or equal (<=), greater or equal (>=) and different from (<> or !=). System databases have an database_id less than or equal to four. Write a query that list all system databases.
  3. All the data types in SQL Server is listed in sys.types. All the base types (that is types which is not derived from another type) have the same value in the column system_type_id and user_type_id. List the name of these types.
  4. All user tables are listed in sys.tables. List the name and create_date for all user tables.

Download answers 7-zip archive.