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

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.

---

Possibly related posts: (automatically generated)

• Remember me code – Forms authentication
• Filtering DML Statements