home articles news code blog links search  
 
         
  Code Samples

Articles and Presentations

sqldbatips.com blog

  
Getting Started with SMO in SQL 2005 - Integrity Checks

Author Jasper Smith Hits 5126
Create Date 30-08-2004 Last Updated 08-08-2007
Versions SQL2005    

Overview

In this series of articles, I'll demonstrate how to use SMO (SQL Management Objects) to do a variety of common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more. These are some of the operations available in the SMO database maintenance utility available on this site - ExpressMaint. In this article we will concentrate on how to examine database integrity using SMO in SQL2005. For information on how to build and compile a SMO application please review Getting Started with SMO in SQL 2005.

Checking Database Integrity

Integrity checks in SMO are methods of the Database object. The table below shows the SMO method and equivalent TSQL command.

SMO Method TSQL Command
CheckAllocations(RepairType.None) DBCC CHECKALLOC WITH NO_INFOMSGS
CheckAllocationsDataOnly() DBCC CHECKALLOC(N'databasename', NOINDEX)
CheckCatalog() DBCC CHECKCATALOG
CheckTables(RepairType.None) DBCC CHECKDB WITH NO_INFOMSGS
CheckTablesDataOnly() DBCC CHECKDB(N'databasename', NOINDEX)

All the methods in the table above return a StringCollection. We can iterate through this collection to return the results of the integrity check as demonstrated in the following code samples.

[Visual Basic]

Imports Microsoft.SqlServer.Management.Smo
Imports System.Collections.Specialized

Module SMOTest
	Sub Main()
        Dim svr As Server = New Server()
        Dim sc As StringCollection		
        Dim db As Database
        db = svr.Databases("AdventureWorks")

        sc = db.CheckTables(RepairType.None)

        For c As Integer = 0 To (sc.Count - 1)
          If sc(c).Length > 0 Then
             Console.WriteLine(sc(c))
          End If
        Next
		
	End Sub
End Module

[C#]

using System;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;

namespace SMOTest
{
    class Program
    {
        static void Main()
        {
            Server svr = new Server();
            Database db;
            db = svr.Databases["AdventureWorks"];

            StringCollection sc;
            sc = db.CheckCatalog();

            for (int i = 0; i < sc.Count; i++)
            { 
	         if (sc[i].Length > 0)
               Console.WriteLine(sc[i]);
            }
        }
    }
}

Related Articles

   Getting Started with SMO in SQL 2005
   Getting Started with SMO in SQL 2005 - Backups
   Getting Started with SMO in SQL 2005 - Restores
   Getting Started with SMO in SQL 2005 - Verifying Backups

Print this page  Print this page Email this article  Email this article Add to Favourites  Add to Favourites
 
         
© Copyright sqldbatips Ltd 2008. Contact webmaster home | news | articles | code | blog | links | search  

free tracking