Get The Database And Table From SqlServer Using SMO (Sql Management Object

This is new technology called SMO(sql management object). You can give the server name and get the total no of database. After the selecting particular database you can got the total no of table and store procedure.

 

Here is the example of the. I used this in the windows application.

 

Steps

1) Create new windows Application (whatever you preferred (vb.net or c#.net))

2) Now add the references on that which are below

2.1) Microsoft.Sqlserver.ConnectionInfo

2.2.) Microsoft.Sqlserver.Smo

2.3) Microsoft.Sqlserver.SmoEnum

3) add the namespace into the project (specifically on form where you want to add)

using Microsoft.SqlServer.Management;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;

4) no of control on the form which are below

4.1 ) button (name : btnGetServer)

4.2) TextBox (name : txtServerName)

4.3) Combobox (name : cmbDataBase)

4.4) Combobox (name : cmbTableName)

4.5) Label (name : lblTotalNoOfTable)

4.6) Combobox (name : cmbSp)

4.7) Label (name : lblTotalNoOfSP)

5) Now On the form at the global level (means at the form level create one Server Variable)

5.1) Server objserver;

6) click on the btnGetServer (it generated the click event of the button) put the below code in to that.

// To Connect to SQL Server

// use the Connection from the System.Data.SqlClient Namespace.

//you can specify the connection string for that

SqlConnection sqlCon = new SqlConnection(@”Data Source=”+ txtServerName.Text+“;uid=(EnterUserId);password=(Password)”);

//build a “serverConnection” with the information of the sqlConnection” ServerConnection serverCon = new ServerConnection(sqlCon);

//The “serverConnection is used in the ctor of the Server.

objserver = new Server(serverCon);

cmbDataBase.Items.Clear();

for (int i = 0; i < objserver.Databases.Count; i++)

{ cmbDataBase.Items.Add(objserver.Databases[i].Name.ToString());

}

 

 

 

 

 

7) on selected index change event of the cmbdatabase put the below code.

//clear the item from cmbsp (combobox)

cmbSP.Items.Clear();

//clear the item from cmbTableName (combobox)

cmbTableName.Items.Clear();

//select the total no of table from the selected databae

for (int i = 0; i < objserver.Databases[cmbDataBase.SelectedItem.ToString()].Tables.Count; i++)

{ cmbTableName.Items.Add(objserver.Databases[cmbDataBase.SelectedItem.ToString()].Tables[i].Name.ToString());

}

//select the total no of storeprocedure from the selected database //(including the system store procedure)

for (int i = 0; i < objserver.Databases[cmbDataBase.SelectedItem.ToString()].StoredProcedures.Count; i++)

{ cmbSP.Items.Add(objserver.Databases[cmbDataBase.SelectedItem.ToString()].StoredProcedures[i].Name);

}

//display information in lable

lblTotalNoOfSP.Text = “NoOfSp In Database Are :” + cmbSP.Items.Count;

lblTotalNoOfTable.Text = “NoOf Table In Database Are :” + cmbTableName.Items.Count;

check in to the application.

Thanks.