SQL 2005 Course

Samples, Utils and Links from MOC 2780 SQL 2005 Course.

Check All Databases with undocumented stored procedure:

EXEC SP_MSForEachDB @command1=”Print ‘?’ DBCC CHECKDB (‘?’)”

Another track to the same station.

Declare @db VarChar(100),
 @dbid int,
 @hidb int

Select @hidb = Max(dbid ), @dbid = 0 from master..sysdatabases

While @dbid < @hidb
Begin
   Set @db = null
   Select @db = name
   From sysdatabases
   Where dbid = @dbid

   if @db is not null
      DBCC CheckDB( @db )

   Set @dbid = @dbid + 1
End

Reindex all tables in a database:

EXEC sp_MSforeachtable @command1=“print ’?’ DBCC DBREINDEX (’?’, ’ ’, 80)”
GO
EXEC sp_updatestats