Click here to Skip to main content
14,693,352 members
Articles » Database » Database » SQL Server
Tip/Trick
Posted 30 Sep 2019

Stats

2.9K views
80 downloads
6 bookmarked

How to Modify Views, Triggers and Procedures from Code

Rate me:
Please Sign up or sign in to vote.
3.50/5 (2 votes)
30 Sep 2019CPOL
Read and/or modify database objects like views, triggers, stored procedures and functions from .NET code.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

NightWizzard
Software Developer (Senior)
Germany Germany
30+ years experience as developer with VB.NET, VB, VBA, VBScript, C#, WPF, WinForms, JavaScript, jQuery, PHP, Delphi, ADO, ADO.NET, ASP.NET, Silverlight, HTML, CSS, XAML, XML, T-SQL, MySQL, MariaDb, MS-ACCESS, dBase, OLE/COM, ActiveX, SEPA/DTAUS, ZUGFeRD, DATEV Format and DATEVconnect, DSGVO, TNT Web-API, MS-Office Addins, etc., including:
- 10+ years experience as developer and freelancer
- 10+ years experience as team leader
- 13+ years experience with CRM solutions

Comments and Discussions

 
QuestionSo why would you want to do this? Pin
kentgorrell1-Oct-19 12:26
professionalkentgorrell1-Oct-19 12:26 
SuggestionNever mind the previous post Pin
Wendelius1-Oct-19 8:05
mveWendelius1-Oct-19 8:05 
QuestionSystem table? Pin
kyy80801-Oct-19 3:05
Memberkyy80801-Oct-19 3:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.