How to Modify Views, Triggers and Procedures from Code

• Download sample project - 135.4 KB

Introduction

The .NET SQLClient library contains a lot of helpful properties and methods to read, write and delete data from SQL databases and to read basic schema information like names of databases, tables and other objects. If you ever used SQL Management Studio to create or change a view, trigger, stored procedure or function, you may have asked yourself, how to manage this task from code at runtime.

This little tip will show you a simple way to read, modify and update all of these database objects that are represented as SQL scripts in SQL Management Studio.

Using the Code

All those database objects as mentioned above are stored as SQL scripts in a system table called [all_sql_modules]. Together with a second system table containing general object properties like names and types, we are able to request the script of the desired object using its name and type:

SELECT m.[definition]
     , m.[uses_ansi_nulls]
     , m.[uses_quoted_identifier]
  FROM [sys].[all_sql_modules] m
    INNER JOIN [sys].[objects] o ON o.[object_id] = m.[object_id]
 WHERE (o.name = '<ObjectName>')
   AND (o.[type] = '<ObjectType>');

The column [definition] will contain the complete ready-to-use SQL script to CREATE the object and the following 2 bit columns will contain flags if constraints for ANSI NULLS and QUOTED IDENTIFIERS should be used when creating or modifying that object.

The object types supported in this sample are:

• V = database view
• T = table trigger
• P = stored procedure
• F = scalar function
• TF = table function

In the next step, the object script may be modified automatically by code or (as shown in the attached sample project) loaded into an editor to let the user make changes.

After modification, the script can be saved to the database by using the ExecuteNonQuery method of a SQL command object. Therefore, we have to replace the CREATE keyword at the beginning of the object script by ALTER (to simply update the object script in database) and perform the constraints (if required) before executing the update command:

SqlCommand objCommand = new SqlCommand();

objCommand.Connection = Connection;
objCommand.CommandType = CommandType.Text;
objCommand.CommandTimeout = 30;

if (bolAnsiNulls)
{
    objCommand.CommandText = "SET ANSI_NULLS ON;";
    objCommand.ExecuteNonQuery();
}

if (bolQuotedIdentifier)
{
    objCommand.CommandText = "SET QUOTED_IDENTIFIER ON;";
    objCommand.ExecuteNonQuery();
}

objCommand.CommandText = ObjectScript;
objCommand.ExecuteNonQuery();

You may download the complete sample project from the link at the top of the page.