Tags: SQL Server Internals, webinar
The link to watch Webinar #1.1: Metadata Overview can be found here: Webinar #1 – SQL Server Metadata Overview
Once you complete the registration information, you will get an email with a link to the webinar. Yes, it’s a little tedious, but the marketing team says I have to use this link. 🙂 The zip file that includes the presentation and demo scripts is located at Webinar 1.1 — SQL Server Metadata Overview files.
As I mentioned when telling you about my webinar series, I will be giving out homework. Of course, it is totally optional, but the problems give you an opportunity to put into practice some of the concepts discussed in the webinar. Here are the problems from the first webinar: Metadata Overview.
- List the names of each database that is in SIMPLE recovery mode
- In the current database, list the name of each user table along with the number of indexes it has.
- In the current database, list just the catalog views.
Here are my solutions. As with most queries, there may be more than one way to write a TSQL statement to get the desired results.
- The first problem was based very closely on an example shown during the broadcast. I pointed out that the databases view had a separate column for each database property. So you could just SELECT from sys.databases looking for the value SIMPLE in the column called recovery_model_desc.
1 2 3 | SELECT name FROM sys.databases WHERE recovery_model_desc = 'SIMPLE'; GO |
- The second problem required that you search through the names of the catalog views, and look at their columns to find the one that contained the indexes for each table. If you ran the query without the filters in the WHERE clause, you might notice that you would get back some objects that were not user tables. I mentioned there was a property function called OBJECTPROPERTYEX, so if you looked up the documentation for that, you would have found the IsUserTable Knowing that you need to exclude any row where index_id was equal to 0 would require a bit more understanding of the sys.indexes view, or it would take looking at the fact that sys.indexes did not call this row an index of any sort. The row with the index_id value of 0 refers to a heap, not to any index.
1 2 3 4 5 6 | SELECT object_name(object_id), COUNT(*) AS IndexCount FROM sys.indexes WHERE index_id > 0 AND OBJECTPROPERTY(object_id, 'IsUserTable') = 1 GROUP BYÂ object_name(object_id); GO |
- The last problem took even more detective work. There is no property that specifies that something is a catalog view, so basically I just solved it by eliminating all the objects that were NOT catalog views. The hardest part was eliminating the INFORMATION SCHEMA VIEWS. If you note that all of their names are in upper case, you could find a way.
1 2 3 4 5 6 7 | SELECT name FROM sys.system_objects WHERE TYPE = 'V' AND name NOT LIKE 'sys%'Â -- eliminate the compatibility views AND name NOT LIKE 'sp[_]%' -- eliminate the special objects AND name NOT LIKE 'dm[_]%' -- eliminate the DMVs AND ascii(name) >= 97;Â -- eliminate the INFORMATION SCHEMA VIEWS GO |
