The programmability section acts as a container for the
different programming tasks that can be done on the SQL Server 2005. This
section includes management screens for creating/editing/deleting Stored
Procedures, managing User Defined functions, Assemblies, Rules, Types whether
user defined types or system types and other major functionalities
Figure 12
As shown in the figure above, when you click the Programmability
node, it lists of all the different functionalities that can be done on the
current database selected are shown.
Stored Procedures
The stored procedures section allows you to create new
stored procedures, modify current ones, delete a stored procedure, and view the
database objects that are dependant on each stored procedure.
Once you click on the Stored Procedures section, a list of
all available stored procedures in the currently selected database is shown
below
Figure 13
Upon clicking on a stored procedure, a stored procedure
specific context menu is shown! You can do many tasks by selecting options from
the context-menu shown.
Clicking on the Details link, you get redirected to a new
screen
Figure 14
There is a section called Parameters, once you click this
folder, a list of all the parameters to that specific stored procedure is
shown.
If you notice the header of the screen in Figure 13 above,
there are five icons:
Modify
View Dependencies
Properties
Permissions
Extended Properties
The modify icon allows you to view the stored procedure TSQL
code
Figure 15
As you can see you can edit the TSQL that makes up the
stored procedure then either cancel or update your changes.
Clicking on the View Details icon allows you to view the
database objects that use the specific stored procedure and the objects that
the stored procedure works on
Figure 16
As shown in Figure 15 above, the stored procedure aspnet_Membership_FindUsersByEmail
depends on a table listed in the section of objects on which the stored
procedure depends upon. However, in this specific stored procedure you can see
that no other database object uses this stored procedure.
The last icon we will discuss in this section is the Permissions
icon. This icon allows you to manage permissions on the stored procedure
Figure 17
Again as in all the screens available in myLittleAdmin, you
can manage the permissions using the script-like window.
Going back to Figure 12 above, the context-menu contains
other options like creating/modifying a stored procedure, viewing dependencies,
renaming, deleting, and other options that we have already discussed in the
above paragraphs.
Modifying or creating a new stored procedure simply open a
TSQL editor in which you can write or modify the stored procedure.
Functions
Functions’ node is the second node beneath the
Programmability node. We will not go through the details of this section since
they are exactly the same as the ones described above for stored procedures.
One difference in this section is that, clicking on the Functions node displays
the functions categorized by their type
Figure 18
Each function type whether the Table-valued functions, that
return data in the form of a data or the Scalar-valued functions, that return a
single value, act as a container for the functions. However, splitting them
this way makes it easier to differentiate those functions and easier to
maintain.
Database Triggers
This section allows you to manage all your database defined
triggers. If no triggers are present you can simply add a new one using the
TSQL window.
Assemblies
SQL Server 2005 acts a host for the .NET CLR. You can create
tables, stored procedures, triggers, and many other objects using your favorite
programming language through Visual Studio 2005. That has been said, when you
create your database objects using a .NET complaint language, as assembly will
be generated and this assembly has to be loaded into the SQL Server for the
objects to be physically created on your database. For more information on SQL
Server CLR Integration, check this article: Introduction to
SQL Server CLR Integration.
This section allows you to view all the assemblies that have
been loaded to SQL Server.
Types
The types section allows you to manage the system data
types, user defined data types, user defined types, and XML Schema Collections
Figure 19
In this section we will create a new user defined data type.
Simply click on the User-defined Data Types and the following screen will be
shown below
Figure 20
We will create a new data type called iq which is of type int.
Rules
Rules are constraints that we can define and apply on the
database objects. MyLittleAdmin allows you to create a new rule using the TSQL
editor.
Defaults
The last section of the Programmability is the Default
sections. Once again you are allowed to create a new Default using the TSQL
editor.
