SQL Metadata Queries for sys.databases and sys.tables

Problem

SQL Server provides lots of information about various aspects of server and database objects and this data is referred to as metadata. Understanding what is available and how to use this metadata can greatly enhance and streamline how you can work with SQL Server.

Solution

SQL Server offers multiple frameworks for processing metadata. You can think of metadata as data about other data. This tip focuses on metadata resources in sys views. A sys view is a built-in SQL Server view that exposes the metadata on a SQL Server instance. Each sys view name starts with a sys prefix, a period, and the name for a class of SQL Server objects. For example, sys.databases has a separate row for each database on a SQL Server instance. Similarly, sys.tables and sys.columns return a separate row for each table or column on a SQL Server database within a SQL Server instance.

You can query sys views with select statements to return a lot of different data. A prior article, “Over 40 queries to find SQL Server tables with or without a certain property," offers an extensive collection of sys view programming examples.

T-SQL for Named and Default SQL Server Instance Names

Whether or not you have multiple SQL Server named instances installed on your computer, you can use sys views and run queries to check for default and/or other named instances of SQL Server. The following two select statements were run on a SQL Server 2019 version with no named instances installed. As a result, the two select list items in the first select statement return identical values for their first and second select items. Similarly, the @@servername item in the second select statement exactly matches the return value from the second select list item in the first select statement.

-- queries for computer name and instance name
 
-- query for named instance with server property
-- when serverproperty('MachineName') = serverproperty('ServerName')
-- then you are running the default instance or you are just connected
-- to the default instance
select 
  serverproperty('MachineName') [computer name],
  serverproperty('ServerName') [instance name]
 
-- query for @@servername; when 
-- @@servername = serverproperty('ServerName')
-- then you are connected to the default instance
-- or you just have a default instance installed on your computer
select @@servername [return value for @@servername]

Listing sys.databases Rows

If you want an overview of the databases on a SQL Server instance, the sys.databases view may be all you need. This view has one row per database on the current SQL Server instance.

• The view’s name column value must be unique within a SQL Server instance; this column has a sysname data type, indicating a nvarchar datatype with up to 128 characters.
• The database_id column is a numeric column automatically populated by SQL Server when the database is initially created. Learn.microsoft.com indicates the data type for the column as int, but valid data types for database_Id are bigint, smallint, tinyint, and numeric or decimal as long as the scale is 0.

-- here is a query to list the database_id values and names
-- of databases on a sql server instance
 
select database_id [database id], name [database name]
from sys.databases;

The following screenshot shows the database_id and name column values for the default SQL Server instance.

• This instance has seven databases with database_id values of 1 through 7.
• The database_id value of 1 points to the master database.
• The next three database_id values point to three other system databases: tempdb, model, and msdb.
• The remaining three databases point to user-defined databases: DataScience, Correlate_index_ETFs_and_ECON_indices, and alter_examples.

Looking Up database_id Values, Name, and create_date Columns from sys.databases

The db_id () function can look up the database_id value for a database whose name appears in the name column of the sys.databases view. Alternatively, you can set the current default database value with a use statement and assign the looked-up database_ID value for the current default database.

The following script illustrates T-SQL syntax for invoking these features.

• The script excerpt starts by invoking the db_id () function to return the database_id value for the DataScience database; the return value from the db_id () function is assigned an alias name of database_id.
• Next, a use statement assigns the master database as the default database for the script; this statement overwrites any other default database for the script; the following select statement displays the database_id for the master database.
• Next, a declare statement defines the @currrent_database_id local variable; this local variable resets to various values throughout the remainder of the script.
• For example, the select statement following the next use statement shows the database_id value for the DataScience database.
• Next, a select statement displays the database_id, name, and create_date column values from a row in sys.databases where the database_id value matches @currrent_database_id.
• Then, a use statement revises the name of the default database to master, which is followed by a set statement for @currrent_database_id that assigns the database_id value for the master database_id to the @currrent_database_id local variable.
• The script example terminates with a select statement that displays the database_id, name, and create_date column values from the sys.databases view where database_id equals @currrent_database_id.

-- here is a query to return the database_id value for the DataScience database
select db_id('DataScience') [database_id]
 
-- here is a query to return the database_id value for the default database
-- after setting the default database to master 
   use master
   select db_id() [database_id]
 
-- declare a local variable to hold the most 
-- recently computed value db_id()
declare @current_database_id int
 
-- set the default database to DataScience
-- and save the most recently computed value of db_id in it
use DataScience
set @current_database_id = db_id()
 
-- return the database_id, name, create_date values for the 
-- database_id pointed at by @current_database_id
select database_id [database_id], name [database name], create_date [create_date]
from sys.databases
where database_id = @current_database_id
 
-- reset the default database to master
-- and save the most recently computed value of db_id in @current_database_id
use master
set @current_database_id = db_id()
 
-- return the database_id, name, create_date values for the 
-- database_id pointed at by @current_database_id
select database_id [database id], name [database name], create_date [create date]
from sys.databases
where database_id = @current_database_id

The preceding script generates four results sets. Two separate databases are successively referenced by the select statements – one for the master database and the other for the DataScience database.

• The master database is created by the SQL Server installation process. If you have an instance of SQL Server installed, you have a master database.
• DataScience is a user-defined database. You can replace its name with the name of any user-defined database that is already on the SQL Service on which you are running the script. If you do not replace direct and indirect references to the DataScience database (and it is not already on your SQL Server instance).
  • The first select statement will return a null value for the db_id () function reference to the DataScience database.
  • The third select statement will fail because of a reference to a non-existent database.

Here is the output that did appear from the preceding script on the test SQL Server instance for the script.

Counting the Number of Tables in One or More Databases

The main purpose of the samples in this section is to introduce the sys.tables view. The sys.tables view has one row for each user-defined table in the current database on the current SQL Server instance. SQL Server manages the underlying data sources for the sys.tables view. Microsoft provides more detailed information about user-defined tables on the sys.tables documentation page.

The following script demonstrates the basics of querying the sys.tables view.

• Unlike the sys.databases view, the sys.tables view requires a preceding reference to a default database. In the same way databases belong to SQL Server instances, SQL Server tables belong to SQL Server databases.
• You can list, filter, count, or join via table identifiers in the sys.tables rows to rows in other SQL Server metadata repositories, such as sys.columns.

The example in this section contains three different select statements demonstrating different approaches to counting the tables in a database on a SQL Server instance. The first and second select statements reference the master database via a use statement. The third select statement references a user-defined database named DataScience.

• The first select statement counts the rows in the sys.tables view for the master database on the current SQL Server instance.
  • The count(*) function in the select statement returns a count of the number of rows in the sys.tables view.
  • The alias name for the count(*) function in the select statement is table name count.
  • The from clause designates sys.tables as the source for the select statement.
• The second select statement also counts the rows in the sys.tables view for the master database. This select statement includes two select list items.
  • The first item is the same count(*) function reference discussed above for the preceding select statement.
  • The second item references the db_name () function to return the name of the current default database
    • The db_name () function frequently is used in T-SQL examples to return the name of a database which depends on a database_id value.
    • In this example, the database_id value for the default database is returned by a subquery for the db_id () function, which returns the database_id value to the outer query with the db_name () function.
• The third select statement explicitly references another database besides the master database – namely, DataScience.
  • For this statement to work as listed in the script below, you must have a database named DataScience on the current SQL Server instance.
  • You can update the name DataScience with the name of any other database on the current SQL Server instance if you prefer to use another database name.

-- specify master as default database
use master
 
-- count of table names in sys.tables in default database
-- where master is designated as default database
select
 count(*) [table name count]
from sys.tables
 
-- display count of table names from sys.tables in default database
-- where master is designated as default database
-- use subquery having a select statement within db_name() function 
-- as a select list item for the outer query 
 
-- specify master as default database
use master
 
select
 count(*) [table name count], db_name((select(db_id())))  [default database name]
from sys.tables
 
-- specify DataScience as default database
use DataScience
 
-- count of table names in sys.tables in default database
-- where DataScience is designated as default database
select
 count(*) [table name count], (select db_name())  [default database name]
from sys.tables

The following screenshot shows the three results sets from the preceding script. The results set for each example resides in a separate window pane.

• Notice that the first and second panes have a count of 14 tables. The actual count, in your case, may differ depending on your configuration selections during SQL Server installation and any post-installation tables you add to the master database.
• The table count for the third pane will also differ depending on the number of tables referenced in the use statement for the third select statement.

Listing Table Name, create_date, and create_date Cast as a Date Data Type Value

Processing file dates can be a useful way of learning about the history of files. The create_date column in the sys.tables view indicates the date during which a table was created in the current database instance. The data type for the create_date column is datetime. This type is tracked in SQL Server to a minimum resolution of .00333 seconds. However, other data types can return other minimum levels of accuracy. Your specific application requirements may require one or more SQL Server data types for tracking file date values. The scripts in this section illustrate this point.

Here is a resource for processing SQL Server datetime values, whether from the sys.tables create_date column or another SQL Server data source. Here are the ranges of values and minimum accuracy values for SQL Server datetime types.

These differing levels of accuracy may present minor challenges when trying to process create_date column values. The three script samples in this section highlight the kinds of issues that you may encounter.

The next script sample displays three columns that draw on the sys.tables view for the master database in the current SQL Server instance.

• The first column displays the table name value for a row in sys.tables.
• The second column displays the create_date column value in its native data type from the sys.tables view; recall that this data type is datetime.
• The third column displays the create_date column value cast in a date type.

-- list name, create_date, and create_date cast as date
-- from sys.tables in the master database
-- for a SQL Server instance
 
use master
 
select 
 name
,create_date
,cast(create_date as date) [create_date cast as date]
from sys.tables

Here is a screenshot with the results set from the preceding code sample. Notice that the second and third columns return different values based on the create_date column from sys.tables.

• For example, the first, second, and third rows show create date values of 2003-04-08 09:18:01.557, 2003-04-08 09:18:02.870, and 2003-04-08 09:18:04.180.
• On the other hand, all the first three rows have create_date cast as date values of 2003-04-08.

Depending on your requirements, this may or may not cause an issue.

Here is a slight twist of the preceding script to return the table name with the minimum create_date value from the sys.tables view. Notice that a where clause is added to the script after the from clause in the preceding script. The where clause sets a requirement for the results set to return any row from sys.tables that matches the minimum create_date value. Because the create_date column has a native datetime data type, this query returns a single row with a file name of spt_fallback_db and a create_date column value of 2003-04-08 09:18:01.557.

use master
 
select 
 name
,create_date
,cast(create_date as date) [create_date as date]
from sys.tables
where create_date =  (select min(create_date) from sys.tables)

Here is the results set from the preceding script. Because the create_date column has a native datetime data type, this query returns a single row with a table name of spt_fallback_db and a create_date column value of 2003-04-08 09:18:01.557.

There is nothing inherently wrong with the preceding script or its results set. On the other hand, what if the requirement was to return any row from sys.tables that matched the date value instead of the datetime value of the earliest create_date in sys.tables? This kind of clarification or objective restatement dictates the need for a new script.

Here is a second modification to the script presented at the beginning of this section. The where clause in the following version of the script has a criterion that returns any row from sys.tables that matches the date value instead of the datetime value of the earliest create_date in sys.tables. This requirement is implemented by computing the minimum date data type value for the create_date column and then including only rows in the result set that match this date value.

use master
 
select 
 name
,create_date
,cast(create_date as date) [create_date as date]
from sys.tables
where cast(create_date as date) =  cast((select min(create_date) from sys.tables) as date)

For the original set of values in the sys.tables view, the following script can return different rows than either of the two preceding scripts. This script aims to return any row from sys.tables that matches the earliest create_date as a date data type value. Because there are three rows with a create_date having a date of 2003-04-08, there are three rows in the results set from this version of the query. These rows appear in the following screenshot. As projects evolve, it is common for requirements to reflect refreshed requests for output. The three script versions of this section may help you tweak code for revised requirements more quickly than before reviewing this section.

Next Steps

The next steps suggest three different directions you can take:

• If you find an example script that addresses a specific metadata query you need to implement, update the sample scripts in the tip for the names of metadata for which you need a results set.
• If you find a need to redesign a query example in this tip, then study the last section, which illustrates how to accomplish this kind of task.
• Finally, you can build on your understanding of metadata queries from this tip and the references cited in this tip to create and populate your library of custom metadata queries.