AzureFeeds

There are times that you need to be able to query the database and its internal structures rather than querying the user tables.

In the example above, I’ve asked for details of all the databases, and then for details of all the schemas in the current database, followed by details for all the tables and columns in the current database.

These are a well-designed set of system views that are easy to work with. They are often referred to as catalog views. You can even see all of them as part of querying sys.views.

These views make it very easy to then write utilities and things that process things to do with the structure of the database itself rather than the user data that it holds.

INFORMATION_SCHEMA

Before we had these catalog views, we needed to use a set of views in the INFORMATION_SCHEMA schema.

In theory, these were a good idea. If every database engine (including SQL Server) provided an implementation of these views, you could write queries to interrogate the structure of many different types of database engine.

Unfortunately, they were never great views, and there are so many useful things that are just missing from them.

I haven’t used them for a very long time, and I’d suggest you avoid them too.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

The post T-SQL 101: #127 Querying the SQL Server System Catalog appeared first on The Bit Bucket.