MS SQL Server
  Home arrow MS SQL Server arrow Retrieving SQL Server 2005 database inform...
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
ASP Web Hosting  
ASP.NET Web Hosting 
Dedicated Servers 
Actuate Whitepapers 
VeriSign Whitepapers 
Windows Web Hosting
 
IBM® developerWorks 
Sun Developer Network 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
MS SQL SERVER

Retrieving SQL Server 2005 database information using SMO: properties of database objects
By: Jagadish Chaterjee
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 8
    2006-02-21

    Table of Contents:
  • Retrieving SQL Server 2005 database information using SMO: properties of database objects
  • How to retrieve all properties of a particular SQL Server database using SMO
  • How to retrieve all properties of a particular SQL Server View using SMO
  • How to retrieve all properties of a particular SQL Server column using SMO

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT

    Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

    Retrieving SQL Server 2005 database information using SMO: properties of database objects


    (Page 1 of 4 )

    This article is the last in a series focusing on retrieving SQL Server 2005 information using SMO together with Visual Basic 2005 and Visual Studio 2005. Some of the topics we will cover in this article include retrieving all properties of an SQL Server Instance, an SQL Server database, an SQL Server table, and so on.
    A downloadable file for this article is available here.

    Since I already explained SMO, DMO and so on in the first article of this series, I shall straight away drive into the technical issues of using SMO.  I also covered the concept of “working with SMO using Visual Studio 2005” in my first article.  If you are new to SMO (or having any trouble working with a new Visual Studio 2005 solution), I strongly suggest you go through that article.

    How to retrieve all properties of a particular SQL Server instance using SMO

    Before proceeding to solve this issue, we need to define a structure which can hold all the properties along with their values.  The structure has been created under “util.vb”.  The code for the structure is as follows:

        Public Function getStructProperty() As DataTable
            Dim dt As New DataTable
            dt.Columns.Add("Name")
            dt.Columns.Add("value")
            Return dt
        End Function

    Now that the structure to hold properties is ready, we can start by retrieving all the properties.  Let us proceed with the following code:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            'to retrieve all properties of SQL Server instance
            Dim svr As Server = New Server (".\sql2k5")
            Dim opt As ConfigProperty
            Dim dtReport As DataTable = util.getStructProperty
            For Each opt In svr.Configuration.Properties
                Dim drReport As DataRow = dtReport.NewRow
                drReport ("Name") = opt.DisplayName
                drReport ("Value") = opt.ConfigValue
                dtReport.Rows.Add (drReport)
            Next
            Me.DataGridView1.DataSource = dtReport
     
        End Sub

    The most important statements from the above code fragment are the following ones:

            Imports Microsoft.SqlServer.Management.Smo

    The above statement imports the necessary SMO namespace (which maintains the central SMO hierarchy).

            Dim svr As Server = New Server(".\sql2k5")

    The above statement connects to the local named instance (“sql2k5”).

            Dim dtReport As DataTable = util.getStructProperty

    The above statement works with the previous routine (which we created in the beginning).

            Dim opt As ConfigProperty
            For Each opt In svr.Configuration.Propertie

    The first statement is used to go through all the properties available.  The “properties” of “server configuration” returns a collection of several “ConfigProperties”.  And the rest is the same.

    More MS SQL Server Articles
    More By Jagadish Chaterjee


       · Hello guys, another beautiful contribution of SQL Server 2005 SMO. Enjoy. Any new...
       · was looking for this information all day.happy that i got it now.i need to display...
     

    MS SQL SERVER ARTICLES

    - Completing the Introduction to Transact-SQL
    - A Brief Introduction to Transact-SQL
    - Lookups and Blocking Bad Data
    - Field Validation Rules for Blocking Bad Data
    - Using Masks to Block Bad Data
    - Blocking Bad Data
    - Using @@ROWCOUNT and TABLE Variables for Dat...
    - How to Use Variables, IF and CASE in Databas...
    - Creating Important Aspects of Notification S...
    - Working wth Variables in Database Interactio...
    - Delving Deeper into Notification Services
    - Notification Services
    - Building a Multi-table Report with SQL 2005 ...
    - A Secure Way of Building Connection Strings
    - Transferring a Database Using the SSIS Desig...





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway