X hits on this document

PDF document

The “SQL Server Management Objects” series - page 2 / 6





2 / 6


The “SQL Server Management Objects” series

As you change the Database from the combo box, the object list under it will change accordingly. As you are aware, objects in SQL Server 2008 and 2005 (e.g. tables, stored procedures, views etc) are identified by a schema name followed by the object name itself. It is important to understand because the same object name under a specified schema may appear in another schema. It is common and allowed in SQL Server 2008 although ultimately the combination of schema and object name should be unique in a SQL Server instance.

This demo program allows you to see all stored procedures, functions, views and triggers in the database; or filter only for a specified object displayed. Those four types are programmable objects that contain SQL script that we are interested in. Be noted, that the list only contains user objects and we exclude the system objects from the list, that’s why we may see a shorter list compared to what is seen in the MS SQL Server Management Studio.

To generate a SQL script, simply choose an object then press the “Generate Script” button. A SQL file will be created immediately comprising lines of T-SQL program of the selected object.

Now stop the program and look into the source code, let us see how the program works. The first line of the Form class contains a constant declaration of the SQL Server instance. This value should be changed according to your SQL Server installation because (local)\SQL2K8 is the instance name that specified in my installation. Next is the declaration of the ServerConnection object to specify the connection parameter to the SQL Server; and finally the Server object as the central of the SMO operation.

... private const string INSTANCE_NAME = "(local)\\SQL2K8"; private Microsoft.SqlServer.Management.Common.ServerConnection conn =

new ServerConnection(INSTANCE_NAME); private Microsoft.SqlServer.Management.Smo.Server server;


p r i v a t e v o i d F o r m 1 _ L o a d ( o b j e c t s e n d e r , E v e n t A r g s e { )

try {

//Login using Windows Authentication conn.LoginSecure = true; conn.Connect(); server = new Server(conn);

//Enumerate the database list foreach (Database db in server.Databases) {

//add the Data to the combobox. cboDatabase.Items.Add(db.Name); } lvObject.Columns.Add("Name", 300); lvObject.Columns.Add("Type", 150); lvObject.Columns.Add("Parent", 150);


  • -


Document info
Document views28
Page views28
Page last viewedTue Jan 17 06:15:39 UTC 2017