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