- By Itzik Ben-Gan
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 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.
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.
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');