Microsoft SQL Server 2012 Bible

Examining SQL Server with Code

One of the benefits of using SQL Server is the cool interface it offers to develop and administer the database. Management Studio is great for graphically exploring a database; T-SQL code, although more complex, exposes even more detail within a programmer's environment.

Dynamic Management Objects

Introduced in SQL Server 2005, dynamic management objects (DMOs) offer a powerful view into the structure of SQL Server and the databases, as well as the current SQL Server status (memory, IO, and so on).

As an example of using DMOs, the next query looks at three DMOs concerning objects and primary keys:

USE AdventureWorks2012; 
GO

SELECT s.NAME + ‘.’ + o2.NAME AS ‘Table', pk.NAME AS ‘Primary Key'
 FROM sys.key_constraints AS pk
  JOIN sys.objects AS o
   ON pk.OBJECT_ID = o.OBJECT_ID
  JOIN sys.objects AS o2
   ON o.parent_object_id= o2.OBJECT_ID
  JOIN sys.schemas AS s
   ON o2.schema_id= s.schema_id;

Result:

Table           Primary Key
-------------------------- --------------------------------------
dbo.ErrorLog        PK_ErrorLog_ErrorLogID
Person.Address       PK_Address_AddressID
Person.AddressType     PK_AddressType_AddressTypeID
dbo.AWBuildVersion     PK_AWBuildVersion_SystemInformationID
Production.BillOfMaterials PK_BillOfMaterials_BillOfMaterialsID
Production.Document    UQ__Document__F73921F793071A63
Person.BusinessEntity   PK_BusinessEntity_BusinessEntityID

sp_help

Sp_help, and its variations, return information regarding the server, database, objects, connections, and more. The basic sp_help ...