Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 345

The next series of steps will include DBCC commands to validate integrity at filegroup level,
table level, and constraint level.

1. The DBCC CHECKFILEGROUP can be used to check allocation and structural integrity

of all tables and views in a specified filegroup. The code is as follows:
--Check allocation and integrity in a specified filegroup of
current database
--CHECKS only PRIMARY filegroup
USE AdventureWorks2008R2;
GO
DBCC CHECKFILEGROUP;
GO

--CHECKS specific filegroup and integrity of the physical
structure of the page
USE AdventureWorks2008R2;
GO
DBCC CHECKFILEGROUP (2, NOINDEX) WITH PHYSICAL_ONLY;
GO

2. The data integrity for tables and indexed views can be checked using DBCC

CHECKTABLE . The steps are simple and self-explanatory:
--Check data integirty for tables and views
DBCC CHECKTABLE ('Person.BusinessEntity') WITH ALL_ERRORMSGS

DBCC CHECKTABLE ('Person.BusinessEntity') WITH ESTIMATEONLY

DBCC CHECKTABLE ('Person.BusinessEntity', 1) WITH PHYSICAL_ONLY

As a best practice, use the DBCC CHECKDB WITH DATA_PURITY
option that will check the database for column values that are out of
range. This option will check column-value integrity by default.

3. Finally, to alert on any CHECK or foreign key constraint violation, the DBCC

CHECKCONSTRAINTS can be used. In order to alert the constraint violation, let us
demonstrate the steps as follows:

--Check for constraints violation on specified table or indexed
view
--Obtain a pre update information
DBCC CHECKCONSTRAINTS ('Person.Address')

--CREATE a CHECK constraint on PostalCode column on the table
ALTER TABLE Person.Address ADD CONSTRAINT CHK_PostalCodeLength