Single-Table Queries in Microsoft SQL Server 2012

Querying Metadata

SQL Server provides tools for getting information about the metadata of objects, such as information about tables in a database and columns in a table. Those tools include catalog views, information schema views, and system stored procedures and functions. This area is documented well in SQL Server Books Online in the “Querying the SQL Server System Catalog” section, so I won’t cover it in great detail here. I’ll just give a couple of examples of each metadata tool to give you a sense of what’s available and get you started.

Catalog Views

Catalog views provide very detailed information about objects in the database, including information that is specific to SQL Server. For example, if you want to list the tables in a database along with their schema names, you can query the sys.tables view as follows.

USE TSQL2012;

SELECT SCHEMA_NAME(schema_id) AS table_schema_name, name AS table_name
FROM sys.tables;

The SCHEMA_NAME function is used to convert the schema ID integer to its name. This query returns the following output.

table_schema_name  table_name
------------------ --------------
HR                 Employees
Production         Suppliers
Production         Categories
Production         Products
Sales              Customers
Sales              Shippers
Sales              Orders
Sales              OrderDetails
Stats              Tests
Stats              Scores
dbo                Nums

To get information about columns in a table, you can query the sys.columns table. For example, the following code returns information about columns in the Sales.Orders table including column names, data types (with the system type ID translated to a name by using the TYPE_NAME function), maximum length, collation name, and nullability.

SELECT
  name AS column_name,
  TYPE_NAME(system_type_id) AS column_type,
  max_length,
  collation_name,
  is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID(N'Sales.Orders');

This query returns the following output.

column_name     column_type     max_length collation_name            is_nullable
--------------- --------------- ---------- ------------------------- -----------
orderid         int             4          NULL                      0
custid          int             4          NULL                      1
empid           int             4          NULL                      0
orderdate       datetime        8          NULL                      0
requireddate    datetime        8          NULL                      0
shippeddate     datetime        8          NULL                      1
shipperid       int             4          NULL                      0
freight         money           8          NULL                      0
shipname        nvarchar        80         Latin1_General_CI_AI      0
shipaddress     nvarchar        120        Latin1_General_CI_AI      0
shipcity        nvarchar        30         Latin1_General_CI_AI      0
shipregion      nvarchar        30         Latin1_General_CI_AI      1
shippostalcode  nvarchar        20         Latin1_General_CI_AI      1
shipcountry     nvarchar        30         Latin1_General_CI_AI      0

Information Schema Views

An information schema view is a set of views that resides in a schema called INFORMATION_SCHEMA and provides metadata information in a standard manner. That is, the views are defined in the SQL standard, so naturally they don’t cover aspects specific to SQL Server.

For example, the following query against the INFORMATION_SCHEMA.TABLES view lists the user tables in the current database along with their schema names.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = N'BASE TABLE';

The following query against the INFORMATION_SCHEMA.COLUMNS view provides most of the available information about columns in the Sales.Orders table.

SELECT
  COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
  COLLATION_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = N'Sales'
  AND TABLE_NAME = N'Orders';

System Stored Procedures and Functions

System stored procedures and functions internally query the system catalog and give you back more “digested” metadata information. Again, you can find the full list of objects and their detailed descriptions in SQL Server Books Online, but here are a few examples. The sp_tables stored procedure returns a list of objects (such as tables and views) that can be queried in the current database.

EXEC sys.sp_tables;

The sp_help procedure accepts an object name as input and returns multiple result sets with general information about the object, and also information about columns, indexes, constraints, and more. For example, the following code returns detailed information about the Orders table.

EXEC sys.sp_help
  @objname = N'Sales.Orders';

The sp_columns procedure returns information about columns in an object. For example, the following code returns information about columns in the Orders table.

EXEC sys.sp_columns
  @table_name = N'Orders',
  @table_owner = N'Sales';

The sp_helpconstraint procedure returns information about constraints in an object. For example, the following code returns information about constraints in the Orders table.

EXEC sys.sp_helpconstraint
  @objname = N'Sales.Orders';

One set of functions returns information about properties of entities such as the SQL Server instance, database, object, column, and so on. The SERVERPROPERTY function returns the requested property of the current instance. For example, the following code returns the product level (such as RTM, SP1, SP2, and so on) of the current instance.

SELECT
  SERVERPROPERTY('ProductLevel');

The DATABASEPROPERTYEX function returns the requested property of the specified database name. For example, the following code returns the collation of the TSQL2012 database.

SELECT
  DATABASEPROPERTYEX(N'TSQL2012', 'Collation');

The OBJECTPROPERTY function returns the requested property of the specified object name. For example, the output of the following code indicates whether the Orders table has a primary key.

SELECT
  OBJECTPROPERTY(OBJECT_ID(N'Sales.Orders'), 'TableHasPrimaryKey');

Notice the nesting of the function OBJECT_ID within OBJECTPROPERTY. The OBJECTPROPERTY function expects an object ID and not a name, so the OBJECT_ID function is used to return the ID of the Orders table.

The COLUMNPROPERTY function returns the requested property of a specified column. For example, the output of the following code indicates whether the shipcountry column in the Orders table is nullable.

SELECT
  COLUMNPROPERTY(OBJECT_ID(N'Sales.Orders'), N'shipcountry', 'AllowsNull');