Using SMOs, Read Tables Script using C#.NET

SQL Server Management Objects (SMOs) are used manipulate database for DBA activities via an application build by you, you can automate some of the DBA tasks by using SMOs in the application.

In this article I am try to explain how we can use SMO to retrieve all the Table listed in Nortwind database and selecting a Table I am display its create script and listing down all the columns in that table.

So lets start create an example how to use SMOs.

Create a Windows Forms application and Add following controls

1) 2 List boxes one is for listing all the tables and one is for listing all the columns in particular table
2) A Text box with multi line mode true and set scrollbars to Vertical

Refer Screen Shot: Design.JPG

Once we have added controls and designed, we need add SMO DLLs for reference so that we can use built-in class.

You need to add following 4 DLL for reference
1) Microsoft.SqlServer.ConnectionInfo
2) Microsoft.SqlServer.Smo
3) Microsoft.SqlServer.SmoEnum
4) Microsoft.SqlServer.SqlEnum

You will find these DLL at following location:
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\

Once you have added DLL import them for use, please refer screen shot: References.JPG

Now we are ready with our design and references part, so let’s start writing code to get all the Tables in the Northwind Database and add to the ListBox1, for this I have to create a Server object which represents a SQL Server in the machine we need to pass the server name to create that object, here I am creating module level variable so that I can use it in other methods also.

Once you have created server object, you need to get the Database object and Database object will have collection of table, read the Table collection and add Table names to ListBox1, please can refer the code below and also screen shot:Form_Load.jpg

Now ListBox1 has all the Tables in the Northwind database on selecting any of the table in ListBox1 we want to show all the columns of that table in Listbox2 and also display Create Script of that table in multi line Text box, for that we need to write code in listBox1_SelectedIndexChanged , we create a Table object using selected Table Name in ListBox1 and read all the columns of that table and display in ListBox2, you can refer below code and also screen shot ListColumns.jpg

Now we are ready with our code just build, run and test.

Hope this helps.