SQL Server Metadata Succinctly®
CHAPTER 1
Introduction
In the classic Star Trek episode called "The Enterprise Incident," the crew steals the Romulan cloaking device and attempts to integrate it into their ship. By reading the device’s metadata, the crew was able to figure out how to make the device work and successfully evade those nasty Romulans. While it seems unlikely that the technology would be similar, most devices provide "metadata" about themselves.
Metadata
Metadata is very common in most digital devices and files. Pictures taken by a digital camera can have over 400 tags of metadata associated with the photo, including type of digital device used, where and when the photo was taken, and so on. Your phone call log also has metadata, like where you called from, who you called, and how long you talked. You can visit this website (or search for “photo metadata” on Google) to get a sense of the data that is stored.
Privacy concerns aside, it is clear that metadata is very prevalent in the digital world.
SQL metadata
Microsoft SQL Server (and other SQL systems) also provide a large amount of data about their servers, users, tables, and stored procedures. In this book, we are going to explore that metadata and provide example scripts and queries to learn a lot of information about your SQL environment.
Information schema
Information schema views are a series of SQL-92 ANSI standard views that are generally found in most SQL systems (Oracle being a notable exception). These views provide information about tables, columns, views, and stored procedures, and for the most part, the queries using these views will work across database platforms.
Note: The ANSI standard is not specific to Microsoft, so some of the field and view names might not use the same terminology that SQL Server uses.
System and data management views
The system and data management views in SQL Server provide the same information the information schema views do, and a whole lot more information that is unique to SQL Server. If you look behind the scenes at the views in INFORMATION_SCHEMA, you will discover they are wrapper views to system tables.
Code Listing 1: Object Definition for information_schema.tables
--------------------------------------------------- -- Script: Peek_Definition.sql -- Look at definition of information_schema view -------------------------------------------------- DECLARE @srcCode VARCHAR(max) SELECT @srcCode = OBJECT_DEFINITION(object_id('INFORMATION_SCHEMA.tables')) PRINT @srcCode -- The following result would be displayed by the PRINT statement CREATE VIEW INFORMATION_SCHEMA.TABLES AS SELECT DB_NAME() AS TABLE_CATALOG, s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME, CASE o.type WHEN 'U' THEN 'BASE TABLE' WHEN 'V' THEN 'VIEW' END AS TABLE_TYPE FROM sys.objects o LEFT JOIN sys.schemas s ON s.schema_id = o.schema_id WHERE o.type IN ('U', 'V') |
The system tables provide much more information, but the information schema views are closer to the ANSI standard, and generally can be used across database products.
Note: The SQL code in this book was tested using SQL 2017, and most of the views and functions will work on older versions of SQL, as well. Scripts that don’t work on older versions will be noted.
Summary
In this book, we will start with the information schema views and provide some handy queries about the server's data tables. We will then explore some of the views that are unique to Microsoft and provide very useful information about SQL Server.
- 1800+ high-performance UI components.
- Includes popular controls such as Grid, Chart, Scheduler, and more.
- 24x5 unlimited support by developers.