I had an itch of curiosity that I wanted to scratch: I wanted to know what, and how many, of each object type that I had in my database. As I love poking around system tables and views, I came up with the following code:
WITH objs AS ( SELECT type_desc AS Object_Type, COUNT(*) AS Object_Count, 1 AS JoinColumn FROM sys.objects --exclude internal tables, service queues, and system tables WHERE TYPE NOT IN ('IT', 'S', 'SQ') GROUP BY type_desc ), tots AS ( SELECT COUNT(*) AS Type_Count, 1 AS JoinColumn FROM sys.objects WHERE TYPE NOT IN ('IT', 'S', 'SQ') ) SELECT Object_Type, Object_Count, Type_Count, CAST((Object_Count * 1.0) / Type_Count * 100 AS INT) AS Type_Pct FROM objs o JOIN tots s ON o.JoinColumn = s.JoinColumn ORDER BY Object_Type;
This is the output produced:
Object_Type Object_Count Type_Count Type_Pct --------------------------- ------------ ----------- ----------- CHECK_CONSTRAINT 16 204 7 DEFAULT_CONSTRAINT 22 204 10 FOREIGN_KEY_CONSTRAINT 15 204 7 PRIMARY_KEY_CONSTRAINT 42 204 20 SQL_SCALAR_FUNCTION 1 204 0 SQL_STORED_PROCEDURE 15 204 7 SQL_TRIGGER 8 204 3 UNIQUE_CONSTRAINT 3 204 1 USER_TABLE 42 204 20 VIEW 40 204 19 (10 row(s) affected)
I find CTEs (Common Table Expressions) to be interesting. I don’t have near enough familiarity with them, so I’m trying to use them more, and this was an excellent opportunity. My original query was this:
SELECT type_desc as Object_Type, COUNT(*) as Object_Count FROM sys.objects WHERE TYPE NOT IN ('IT', 'S', 'SQ') GROUP BY type_desc ORDER BY type_desc;
And while it did the trick, I was wanting, for no particular reason, to also have the total number of objects and the percentage. Again, no particular reason. It might be able to be done with a window function, but that is also something that I have limited familiarity with, so I decided to approach it as a CTE. And it works nicely. The objs CTE gives me a count of each object type while the tots CTE gives me the count of all objects. By giving each CTE a column with the value of 1, it’s easy to join them together then calculate a percentage.
It was a pleasant little exercise in coding for a utility that scratched my itch. And I hope you had a pleasant holidays, ours were spent in Phoenix where we fortuitously avoided a storm with 60-80 MPH winds that did quite a number on the observatory that employs my wife.