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 ...
Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.