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:
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>');
[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
SqlCommand objCommand = new SqlCommand();
objCommand.Connection = Connection;
objCommand.CommandType = CommandType.Text;
objCommand.CommandTimeout = 30;
objCommand.CommandText = "SET ANSI_NULLS ON;";
objCommand.CommandText = "SET QUOTED_IDENTIFIER ON;";
objCommand.CommandText = ObjectScript;
You may download the complete sample project from the link at the top of the page.