Section 11.1. Programming SMO Instance Classes for Administering Database Objects Not Used for Data Storage


11.1. Programming SMO Instance Classes for Administering Database Objects Not Used for Data Storage

The following SQL Server objects are considered not to store data. They identify the SMO instance classes that administer them.

Server configuration
Registered servers
Linked servers
Database defaults
Data files, log files, and filegroups
Partition functions and schemes
Logins
Users
Server, database, and application roles
Server and database permissions
.NET Framework assemblies
Endpoints
XML schemas
Languages
Statistics
Certificates
Credentials
Symmetric and asymmetric keys
Synonyms
System- and user-defined messages
Full-Text Search
OLE DB provider

A reference to the SMO classes that implement this functionality is included in the "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference" section at the end of this chapter.

This section shows how to programmatically use SMO instance classes that are not used for data storage. The examples in this section are all built using Visual Studio 2005. You need a reference to the following assemblies to compile and run the examples:

  • Microsoft.SqlServer.ConnectionInfo

  • Microsoft.SqlServer.Smo

Additional assembly references for examples will be indicated where required.

11.1.1. Registered Server and Server Groups

Registered servers let you save connection information for SQL Servers. Server groups create a hierarchy similar to an operating system directory to facilitate organization of registered servers. You can view registered servers and server groups in the Registered Servers pane in SQL Server Management Studio.

The SMO RegisteredServer class represents a registered server. Similarly, the ServerGroup class represents a group of registered servers. These classes reside in the Microsoft.SqlServer.Management.Smo.RegisteredServers namespace. The static SqlServerRegistrations property of the SmoApplication class representing the SMO application contains collections of both registered servers and server groups called RegisteredServers and ServerGroups. The following example enumerates registered servers and server groups using these collections.

The SMO classes used to manage registered servers and server groups are described in Table 11-2 in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference" later in the chapter.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Smo.RegisteredServers;     class Program     {         static void Main(string[] args)         {             Console.WriteLine("---SERVER GROUPS---");             foreach (ServerGroup sg in                 SmoApplication.SqlServerRegistrations.ServerGroups)             {                 Console.WriteLine(sg);                 foreach (RegisteredServer rs in sg.RegisteredServers)                     Console.WriteLine("  " + rs.Name);             }             Console.WriteLine(Environment.NewLine + "---REGISTERED SERVERS---");             foreach (RegisteredServer rs in                 SmoApplication.SqlServerRegistrations.RegisteredServers)                 Console.WriteLine(rs.Name);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Sample results are shown in Figure 11-1, indicating that one server group and one server are defined.

Figure 11-1. Results for enumerating server groups and servers example


11.1.2. Managing Logins

This example reads the Logins property of the Server object. Logins is a collection of Login objects, representing all the logins defined on the target server. A Login object represents a SQL Server login account granted access to SQL Server through either Windows or SQL Server standard authentication.

For each Login object found in the Logins collection, the example displays the login name (Login.Name), the default database (Login.DefaultDatabase), and the login mode (Login.WindowsLoginAccessType).

The SMO classes used to manage logins are described in Table 11-8 in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter.

You need to add a reference to the Microsoft.SqlServer.SqlEnum assembly to compile and execute this example.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             foreach (Login l in server.Logins)             {                 Console.WriteLine("Name: " + l.Name);                 Console.WriteLine("DefaultDatabase: " + l.DefaultDatabase);                 Console.WriteLine("WindowsLoginAccessType:" +                     l.WindowsLoginAccessType);                 Console.WriteLine(  );             }             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 11-2.

Figure 11-2. Partial results for enumerate logins example


The results are the same as opening the Security Logins node in the Object Explorer window of SQL Server Management Studio.

This example creates a new login called TestLogin. It does so by instantiating a Login object, setting its LoginType to LoginType.SqlLogin, and then invoking its Create( ) method.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Login l = new Login(server, "TestLogin");             l.LoginType = LoginType.SqlLogin;             l.Create("tempPassword");             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

If you specify a LoginType of SqlLogin, you must set the Password property using one of the overloads of the Create( ) method. If you specify a LoginType of WindowsUser, you must specify the server name and the Windows account name as the login account name argument to the Login class constructor. The following lines of code create a Windows user login:

     Login l = new Login(server, @"serverName\windowsAccountName");     l.LoginType = LoginType.WindowsUser;     l.Create(  ); 

11.1.3. Managing Users

This example enumerates the Users property of a Database object representing the AdventureWorks database. Users is a collection of User objects, which represent SQL Server userssecurity principals used for controlling access permission within a database. While you create logins at the server level, users map to a single SQL Server login in the database where the user is defined. The DatabaseMapping class describes how logins map to database users.

For each user in the Database.Users collection, the example displays the username (User.Name), creation date (User.CreateDate), associated login (User.Login), and user type (User.UserType).

The SMO classes used to manage users are described in Table 11-9 in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter.

You need to add a reference to the Microsoft.SqlServer.SqlEnum assembly to compile and execute this example.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             foreach (User u in db.Users)             {                 Console.WriteLine("Name: " + u.Name);                 Console.WriteLine("CreateDate: " + u.CreateDate);                 Console.WriteLine("Login: " + u.Login);                 Console.WriteLine("UserType: " + u.UserType);                 Console.WriteLine(  );             }             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 11-3.

Figure 11-3. Results for enumerate users example


The results are the same as opening the Security Users node for a database in the Object Explorer window of SQL Server Management Studio.

This example creates a new user, TestUser, in the AdventureWorks database and associates it with the TestLogin SQL Server login account created in the example earlier in this section. It does so by instantiating a new User object, setting its Login property to the string "TestLogin", and then calling its Create( ) method.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["ProgrammingSqlServer2005"];             User u = new User(db, "TestUser");             u.Login = "TestLogin";             u.Create(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

11.1.4. Managing Roles

This example enumerates the roles in the AdventureWorks database. It does so by reading the Roles property of the Database object. Each element in the Roles collection is a DatabaseRole object. The example displays the name (DatabaseRole.Name), creation date (DatabaseRole.CreateDate), and owner (DatabaseRole.Owner) for each DatabaseRole object, and also prints the role members by calling the DatabaseRole.EnumMembers( ) method.

The SMO classes used to manage server roles are described in Table 11-10. The SMO classes used to manage database roles are described in Table 11-11. Both tables are located in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter

You need to add a reference to the Microsoft.SqlServer.SqlEnum assembly to compile and execute this example.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             foreach (DatabaseRole dr in db.Roles)             {                 Console.WriteLine("Name: " + dr.Name);                 Console.WriteLine("CreateDate: " + dr.CreateDate);                 Console.WriteLine("Owner: " + dr.Owner);                 Console.WriteLine("Members:");                 foreach(string s in dr.EnumMembers(  ))                     Console.WriteLine("  " + s);                 Console.WriteLine(  );             }             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 11-4.

Figure 11-4. Results for enumerate roles example


The results are the same as opening the Security Roles Database Roles node for a database in the Object Explorer window of SQL Server Management Studio.

This example adds the user TestUser created in an example earlier in this section to the db_backupoperator role. It does so by calling the AddToRole( ) method of the User class.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["ProgrammingSqlServer2005"];             User u = db.Users["TestUser"];             u.AddToRole("db_backupoperator");             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Alternatively, you can instantiate a specific DatabaseRole object and add the user to the role by using the AddMember( ) method:

     DatabaseRole r = db.Roles["db_backupoperator"];     r.AddMember("TestUser"); 

11.1.5. Managing Server Permissions

This example grants, denies, and revokes server permissions to and from the SQL server login account TestLogin created in the "Managing Logins" section earlier in this chapter. It uses several SMO classes to accomplish this:


Server

Exposes methods Grant( ), Deny( ), Revoke( ), and EnumServerPermissions( ) used to retrieve and specify server permissions.


ServerPermissionInfo

Captures the set of server permissions returned by EnumServerPermissions( ).


ServerPermissionSet

Passes a set of server permissions to the Server methods Grant( ), Deny( ), and Revoke( ).


ServerPermission

Represents a single permission in a set of server permissions.

The SMO classes used to manage server permissions are described in Table 11-12 in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter.

     using System;     using System.Data;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             ServerPermissionInfo[] spi;             ServerPermissionSet sps;             spi = server.EnumServerPermissions("TestLogin");             for (int i = 0; i < spi.Length; i++)                 Console.WriteLine(spi[i].ToString(  ));             Console.WriteLine(  );             // grant "create any database"             sps = new ServerPermissionSet(ServerPermission.CreateAnyDatabase);             server.Grant(sps, "TestLogin");             // deny "view any database"             sps = new ServerPermissionSet(ServerPermission.ViewAnyDatabase);             server.Deny(sps, "TestLogin");             spi = server.EnumServerPermissions("TestLogin");             for (int i = 0; i < spi.Length; i++)                 Console.WriteLine(spi[i].ToString(  ));             Console.WriteLine(  );             // revoke previous grant and deny             sps = new ServerPermissionSet(new ServerPermission[] {                 ServerPermission.CreateAnyDatabase, ServerPermission.ViewAnyDatabase});             server.Revoke(sps, "TestLogin");             spi = server.EnumServerPermissions("TestLogin");             for (int i = 0; i < spi.Length; i++)                 Console.WriteLine(spi[i].ToString(  ));             Console.WriteLine(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 11-5.

Figure 11-5. Results for managing server permissions example


11.1.6. Enumerating .NET Framework Assemblies

SQL Server 2005 through CLR integration lets you create database objects such as functions, stored procedures, triggers, user-defined data types, and user-defined aggregate functions from .NET Framework assemblies.

The following example outputs a list of all .NET Framework assemblies defined in the AdventureWorks database and lists the files associated with each. It does so by enumerating the Assemblies property of the Database object, which is a collection of SqlAssembly objects. SqlAssembly in turn contains a collection called SqlAssemblyFiles, whose elements are SqlAssemblyFile objects.

The SMO classes used to manage .NET Framework assemblies are described in Table 11-15 in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter.

     using System;     using System.Data;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             foreach (SqlAssembly sa in db.Assemblies)             {                 Console.WriteLine(sa.Name + " " + sa.CreateDate);                 foreach (SqlAssemblyFile saf in sa.SqlAssemblyFiles)                     Console.WriteLine("  " + saf.Name);             }             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 11-6.

Figure 11-6. Results for enumerate assemblies example


The SqlAssembly class has methods that let you create, alter, and drop .NET Framework assemblies from the database.

11.1.7. Statistics

Statistics contain information about the distribution of values in a column. The query optimizer uses statistics to calculate the optimal query plan. Specifically, statistics help it estimate the cost of using an index or column to evaluate the query.

When the AUTO_CREATE_STATISTICS database option is set to ON (the default), SQL Server automatically stores statistical information about indexed columns and columns without indexes that are used in a predicate. You might need to manually define statistics, especially if you have disabled automatic statistics on SQL Server. When the AUTO_UPDATE_STATISTICS database option is set to ON (the default), SQL Server periodically updates the statistics as data in the underlying tables changes. Out-of-date statistics can cause the query optimizer to make suboptimal decisions about how to process a query. SQL Server 2005 introduces the AUTO_UPDATE_STATISTICS_ASYNC database option, which, when set to ON (the default is OFF), allows for asynchronous automatic updating of statistics. This lets queries continue to use out-of-date statistical information while it is being updated, rather than blocking the query until the update is complete.

You can view the status of the statistics database options by querying the sys.databases catalog view:

     SELECT  name,             is_auto_create_stats_on,             is_auto_update_stats_on,             is_auto_update_stats_async_on     FROM sys.databases 

This example uses SMO to display the columns in each statistics counter on the HumanResources.Employee table in AdventureWorks. It instantiates a Table object representing HumanResources.Employee. The Table object exposes the statistic counters defined for the table as a collection of Statistic objects. The example scans this collection, enumerating the StatisticColumns object containing the collection of columns defined in the statistics counter.

The SMO classes used to manage statistics are described in Table 11-19 in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             Table t = db.Tables["Employee", "HumanResources"];             StatisticCollection sc = t.Statistics;             for (int i = 0; i < sc.Count; i++)             {                 Console.WriteLine(sc[i].Name);                 foreach (StatisticColumn scol in sc[i].StatisticColumns)                     Console.WriteLine("  " + scol.Name);                 Console.WriteLine(  );             }             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 11-7.

Figure 11-7. Results for displaying statistics counter columns example


This example creates a new statistics counter named IX_Employee_ContactID on the ContactID column of the HumanResources.Employee table in AdventureWorks. It first creates a Table object representing this table. Next it instantiates a new Statistic object, and then associates it with ContactID in a new StatisticColumn object. It adds this StatisticColumn object to the StatisticColumns collection of the Statistic object. Finally, it calls Statistic.Create( ).

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             Table t = db.Tables["Employee", "HumanResources"];             Statistic s = new Statistic(t, "IX_Employee_ContactID");             StatisticColumn sc = new StatisticColumn(s, "ContactID");             s.StatisticColumns.Add(sc);             s.Create(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

You can see the new statistics counter by opening the Databases AdventureWorks Tables HumanResources.Employee Statistics node in the Object Explorer window of SQL Server Management Studio.

This following code drops the statistics counter IX_Employee_ContactID created in the preceding example:

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             Table t = db.Tables["Employee", "HumanResources"];             t.Statistics["IX_Employee_ContactID"].Drop(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

11.1.8. Synonyms

A synonym is a database object that provides an alternate name for another database object either on the local server or on a remote server. From a design point of view, a synonym provides a layer of abstraction that allows changes to be made to underlying objects without affecting client applications. A synonym can be created for the following database objects:

  • Tables, including global and local temporary tables

  • Views

  • SQL scalar functions, inline table-valued functions, table-valued functions, and stored procedures

  • CLR stored procedures, scalar functions, table-valued functions, and aggregate functions

  • Replication-filter procedures

  • Extended stored procedures

A synonym cannot reference a user-defined aggregate function. A synonym object cannot be the base object for another synonym.

This example creates a synonym for the HumanResources.Employees table and uses the synonym to query the table. It instantiates a new Synonym object, associating it with the AdventureWorks database. It then sets several relevant properties:

  • Schema and BaseSchema are both set to "HumanResources".

  • BaseDatabase is set to the AdventureWorks database.

  • BaseObject is set to the name of the underlying object, in this case the Employee table.

Finally, it calls the Synonym.Create( ) method.

The SMO classes used to manage synonyms are described in Table 11-23 in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter.

     using System;     using System.Data;     using System.Data.SqlClient;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             Synonym s = new Synonym(db, "EmployeeSynonym");             s.Schema = "HumanResources";             s.BaseDatabase = "AdventureWorks";             s.BaseSchema = "HumanResources";             s.BaseObject = "Employee";             s.Create(  );             SqlConnection conn = new SqlConnection(                 "Data Source=localhost;Integrated Security=SSPI;" +                 "Initial Catalog=AdventureWorks");             SqlDataAdapter da = new SqlDataAdapter(                 "SELECT TOP 5 EmployeeID, LoginID, Title " +                 "FROM HumanResources.EmployeeSynonym", conn);             DataTable dt = new DataTable(  );             da.Fill(dt);             foreach (DataRow row in dt.Rows)                 Console.WriteLine(row["EmployeeID"] + ", " +                     row["LoginID"] + ", " + row["Title"]);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 11-8.

Figure 11-8. Results for synonym example


You can see the new synonym by opening the Databases AdventureWorks Synonyms node in the Object Explorer window of SQL Server Management Studio.

The following code drops the synonym named EmployeeSynonym created in the preceding example:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             db.Synonyms["EmployeeSynonym", "HumanResources"].Drop(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

11.1.9. Messages

The sys.messages catalog view contains a row for each system-defined and user-defined message in the SQL Server instance. Messages with IDs less than 50001 are reserved for system messages. Catalog views are the recommended mechanism for accessing information in system tables. The sys.sysmessages view contains similar information as the sys.messages catalog view and is provided for backward compatibility.

The SMO class representing a system message is SystemMessage. The Server object exposes the collection of SystemMessage objects through its SystemMessages property. The following example enumerates the collection of system messages for the local machine, and lists the ID, language ID, and text for each of them.

The SMO classes used to manage messages are described in Tables 11-24 and 11-25. Both tables are located in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             foreach (SystemMessage sm in server.SystemMessages)             Console.WriteLine(sm.ID + "[" + sm.Language + "]: " + sm.Text);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Partial results are shown in Figure 11-9.

The following code directly accesses the message with an ID of 105:

     Server server = new Server("localhost");     SystemMessage sm = server.SystemMessages[105, "us_english"]; 

In addition to an indexer, the SystemMessageCollection class also has two methods for getting a specific SystemMessage object. The methods are ItemByIdAndLanguage( ) and ItemByIdAndLanguageID( ), both of which take two arguments, like the indexer. The first argument is the message ID for both methods. The second argument specifies the language as a string for the first method and as a language ID for the second method.

The sys.syslanguages catalog view contains a row for each language installed on the SQL Server instance. The SystemMessage indexer and the accessor methods require either the language ID (langid column) or name (name column) from this table as the language argument.

Figure 11-9. Partial results for enumerating messages example


Working with user-defined messages is similar to working with system-defined messages with the exception that you can create, alter, and drop them using the UserDefinedMessage class. The following example shows how:

     Server server = new Server("localhost");     UserDefinedMessage udm = new UserDefinedMessage(server, 50001,         "us_english", 1, "test user-defined message", false);     udm.Create(  ); 

The following code drops the user-defined message:

     Server server = new Server("localhost");     UserDefinedMessage udm = server.UserDefinedMessages[50001, "us_english"];     udm.Drop(  ); 

11.1.10. Full-Text Search

Full-Text Search lets you index text data in SQL Server and perform linguistic searches against the words and phrases in the data by using rules of the language that the data is in. You can create full-text indexes on char, varchar, and nvarchar data type columns, as well as columns that contain formatted binary data stored in varbinary(max) or image columns. You can build a full-text index on a table that has a single unique column that does not allow NULL values.

A full-text index stores information about significant words in a column.

A full-text index catalog contains zero or more full-text indexes. Each catalog contains indexing information for one or more tables in the database.

This example creates a full-text index on the Description column of the Production.ProductDescription table in AdventureWorks. These are the steps:

  1. It instantiates a FullTextCatalog object, sets its IsDefault property to true, and then calls its Create( ) method.

  2. It associates a Table object with Production.ProductDescription.

  3. It instantiates a FullTextIndex object, linking it to the Table created in Step 2.

  4. It creates a FullTextIndexColumn object tied to the Description column.

  5. It adds the FullTextIndexColumn object created in Step 4 to the IndexedColumns collection of the FullTextIndex object created in Step 3.

  6. It sets the CatalogName property of the FullTextIndex object to point to the FullTextCatalog object created in Step 1.

  7. It calls the Create( ) method of the FullTextIndex object.

The SMO classes used to manage Full-Text Search catalogs and indexes are described in Table 11-26 in the section "SMO Instance Classes for Administering Objects Not Used for Data Storage Reference," later in this chapter.

     using System;     using System.Data;     using System.Collections;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             FullTextCatalog ftc = new FullTextCatalog(db, "PSS2005_AW_Catalog");             ftc.IsDefault = true;             ftc.Create(  );             Table t = db.Tables["ProductDescription", "Production"];             FullTextIndex fti = new FullTextIndex(t);             fti.IndexedColumns.Add(new FullTextIndexColumn(fti, "Description"));             fti.UniqueIndexName = "PK_ProductDescription_ProductDescriptionID";             fti.CatalogName = "PSS2005_AW_Catalog";             fti.Create(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

You can see the new full-text index by opening the Databases AdventureWorks Storage Full Text Catalogs node in the Object Explorer window of SQL Server Management Studio.



Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net