Sql server® 2012 t-sql fundamentals

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 descrip-

tions 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;

www.it-ebooks.info

---

90  

Microsoft SQL Server 2012 T-SQL Fundamentals

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 fol-

lowing 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 ex-

ample, 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');

www.it-ebooks.info

---