Sql server® 2012 t-sql fundamentals

Querying Metadata

SQL Server provides tools for getting information about the metadata of objects, such as informa-

tion about tables in a database and columns in a table. Those tools include catalog views, informa-

tion 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), maxi-

mum 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');

www.it-ebooks.info

---