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