The documentations are an important part for every developer. In this article I am trying to discuss the self-documenting process of database by using extended properties.
Before writing this article, I read several article for various sources related to extended properties to collect facts.
Introduction
Extended properties are the route to creating self-documenting databases. That means one can attach the documentation directly to the database itself, rather than create a separate document.
Extended properties can be used in trigger, stored procedure, function, table, view, parameter, index, constraint or column. One can also use extended properties to document details such as the date of revision, author, input mask, caption, history, rendering-style, and so on.
The advantage of using the extended properties is that the documentation, notes, and so on stay with the databases and can be used as a live documentation. They are backed up with the database, and scripted out with the build scripts.
Extended properties and third-party
Extended properties allow us to document our database objects but it has been left to third-party utilities such as SQL Doc and DBDesc to exploit the use of these properties for generating the full documentation of the database from the database itself.
Disadvantage of Extended properties
The common disadvantage of extended property is it is not replicated with synchronisation process. We have to do it manually by others tools like SQL Compare.
In the Information_Schema view the extended properties is not provided, which would have made to make it easier to access them from SQL.
The standard property name of the extended properties is MS_Description and it is used by third-party software vendors for other purposes, such as storing parameters for entity-relationship diagrams.
How we can use the Extended Properties
Microsoft just provides only one property named MS_Description, which can be used from both Enterprise Manager and SSMS to provide a description of the object to which it is bound. Further, the Diagram Designer provides a description field, accessible via the custom view, which provides an easy way of viewing and editing the documentation of the columns.
Extended properties are stored in sysproperties, and can be accessed by a number of stored procedures are mentioned bellow.
sp_addextendedproperty
|
Adds a new extended property to a database object
|
sp_dropextendedproperty
|
Removes an extended property from a database object
|
sp_updateextendedproperty
|
Updates the value of an existing extended property
|
fn_listextendedproperty
|
Retrieves the value of an extended property or the list of all extended properties from a database object
|
Example
/*we add the extended property to provide a description to the
dbo.Customer.InsertionDate column */
sp_addExtendedProperty
'MS_Description',
'the date at which the row was created',
'user', 'dbo', 'table', 'Customer',
'column', 'InsertionDate'
-- alternative syntax for SQL 2005
sp_addExtendedProperty
'MS_Description',
'the date at which the row was created',
'schema','sales', 'table', 'Customer',
'column', 'ModifiedDate'
/* and then update the description of the
dbo.Customer.InsertionDate column */
sp_UpdateExtendedProperty
'MS_Description',
'the full date at which the row was created',
'user', 'dbo', 'table', 'Customer',
'column', 'InsertionDate'
/* we can list this column */
SELECT * FROM ::fn_listExtendedProperty
( 'MS_Description','user', 'dbo', 'table',
'Customer', 'column', 'InsertionDate')
/* or all the properties for the table column of dbo.Customer*/
SELECT * FROM ::fn_listExtendedProperty
(DEFAULT,'user', 'dbo', 'table', 'Customer', 'column',DEFAULT)
/* And now we drop the MS_Description property of
dbo.Customer.InsertionDate column */
sp_dropExtendedProperty
'MS_Description',
'user', 'dbo', 'table',
'Customer', 'column', 'InsertionDate'
Hope you like it.
Related to Extended Stored Procedure (xp_)
Related to Extended stored procedure, I find some very good article related to it. I think this published resources can help you to build you solid knowledge related to it.
If you've worked with versions of SQL Server prior to SQL Server 2005, you are probably familiar with extended stored procedures. These stored procedures reside in the master database and have names that begin with xp_. Extended stored procedures are invoked and managed similarly to regular stored procedures. You can grant and revoke permissions on extended stored procedures as you do for normal stored procedures. Although extended stored procedures reside in the master database name when it is invoked from a database other than master, as in the following example: database like system procedures, the procedure name has to be fully qualified with the master
exec master..xp_fixeddrives
Extended stored procedures are not built with T-SQL commands; instead, they map to a function stored in a DLL. Historically, extended stored procedures were the mechanism available to extend SQL Server functionality. However, the introduction of CLR procedures provides a much easier, safer way to extend the functionality of SQL Server 2008.
Extended stored procedures are typically written in Microsoft C or Visual C++, using the Microsoft Extended Stored Procedure API, and coding them can be quite complex. In addition, extended stored procedures run under the same security context as SQL Server and within the same address space. A poorly written extended stored procedure could bring down the SQL Server service. CLR procedures, in contrast, are written in .NET code that is type safe and runs within the App domain boundary so it cannot access random SQL Server memory locations. In other words, it is much easier and safer to create and deploy CLR procedures than extended stored procedures.
Because of the unsafe nature of extended stored procedures, and the greater security and capabilities of CLR stored procedures, extended stored procedures are a feature that will very likely be removed in some future version of Microsoft SQL Server. For new development efforts, you should use CLR procedures instead of extended stored procedures. In addition, you should make plans to convert any existing applications that currently use extended stored procedures to use CLR procedures instead.
Adding Extended Stored Procedures to SQL Server
If you happen to have a DLL that contains one or more extended stored procedures you need to add to SQL Server, you can use the sp_addextendedproc system stored procedure. Only SQL Server system administrators can add extended stored procedures to SQL Server. The syntax is as follows:
sp_addextendedproc [ @functname = ] 'procedure' ,
[ @dllname = ] 'dll'
Extended stored procedures are added only in the master database. Thesp_addextended procedure adds an entry for the extended stored procedure to the system catalogs and registers the DLL with SQL Server. You must provide the complete path for the DLL when registering it with SQL Server.
To remove an extended procedure from SQL Server, you use sp_dropextendedproc:
sp_dropextendedproc [ @functname = ] 'procedure'
Because extended stored procedure DLLs and SQL Server share the same address space, poorly written extended procedure code can adversely affect SQL Server functioning. Any memory access violations or exceptions thrown by an extended stored procedure could possibly damage SQL Server data areas. For this reason, it is strongly recommended that CLR procedures be considered as an alternative to extended stored procedures. If there is some compelling reason to use extended stored procedures, they should be thoroughly tested and verified before they are installed.
Obtaining Information on Extended Stored Procedures
To obtain information on the extended stored procedures in SQL Server, you use sp_helpextendedproc as follows:
sp_helpextendedproc [ [@funcname = ] 'procedure' ]
Extended Stored Procedures Provided with SQL Server
Most of the extended stored procedures that ship with SQL Server are undocumented. All extended stored procedures (or rather, the references to them) are stored in the master database. You can display them in SSMS under the master database. To do so, you open the Programmability folder for the master database and then open the Extended Stored Procedures folder. The provided extended stored procedures are listed in the System Extended Stored Procedures folder.
If you plan to use an undocumented extended stored procedure, be careful. First, you have to find out what it does and what parameters it takes. You should also be aware that Microsoft does not support the use of undocumented extended stored procedures. Moreover, an undocumented procedure might not be included in a later version of SQL Server, or if it is included, it might behave differently than it does now.
Table contains the lists the general categories of extended stored procedures.
Extended Stored Procedures Categories
| |
Category
| |
Description
|
General extended procedures
| |
Provide general functionality. Perhaps the most useful is xp_cmdshell, which executes external programs and returns the output from them as a result set.
|
SQL Mail extended procedures
| |
Enable you to perform email operations from within SQL Server.
|
SQL Server Profiler extended procedures
| |
Are used by SQL Server Profiler. They can also be used directly, for instance, to create a trace queue and start the trace from within a stored procedure.
|
OLE automation procedures
| |
Allow SQL Server to create and use OLE automation objects.
|
API system stored procedures
| |
Are undocumented extended stored procedures used by the API libraries. The server cursor functionality, for instance, is implemented as a set of extended stored procedures.
|
One of the most useful, and potentially dangerous, extended stored procedures provided with SQL Server 2008 is xp_cmdshell. xp_cmdshell can execute any operating system command or program available on the SQL Server system, as long as it is a console program that doesn't require user input. xp_cmdshell accepts a varchar(8000) (or nvarchar(4000)) value as the command string to be executed, and it returns the results of the command as a single nvarchar(255) column. The full syntax of xp_cmdshell is as follows:
xp_cmdshell { 'command_string' } [ , no_output ]
If the no_output option is specified, the results from the command are not displayed. The following example uses xp_cmdshell to list the files in a directory on the SQL Server computer's hard disk:
EXEC xp_cmdshell 'DIR c:\*.*'
xp_cmdshell runs synchronously. Control is not returned to the SQL Server user session until the shell command completes. This is why you have to ensure that the shell command invoked via xp_cmdshell does not prompt for user input. Commands invoked via xp_cmdshell do not run interactively, so there is no way to respond to the user input prompt. The SQL Server session waits indefinitely for a command invoked via xp_cmdshell to return.
After SQL Server passes off the xp_cmdshell command to the operating system, SQL Server cannot interact with the command. If the command requires user input, the process waits indefinitely, and it usually doesn't go away without a fight. Killing the process in SQL Server usually just leaves it in a KILLED/ROLLBACK state. Closing the session that invoked the xp_cmdshell statement doesn't help either. Sometimes, you might have to stop and restart SQL Server to make the process finally go away. Alternatively, you may be able to use Task Manager on the system where SQL Server is running to identify the system process that corresponds to the process invoked by xp_cmdshell and end the process.
If xp_cmdshell is invoked from another database, it has to be fully qualified as master..xp_cmdshell. Unlike with system procedures, SQL Server doesn't automatically look for extended stored procedures in the master database.
Because of the potentially dangerous nature of xp_cmdshell (it essentially allows a user to run operating system–level commands on the SQL Server machine), it is disabled by default. To enable xp_cmdshell, you must run the following commands:
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO
As an additional security measure in SQL Server 2008, by default, permission to execute xp_cmdshell is limited to users with CONTROL SERVER permission. The Windows process spawned by xp_cmdshell runs within the security context of the account under which the SQL Server service is running. Essentially, it has the same security rights as the SQL Server service account.
When xp_cmdshell is invoked by a user who is not a member of the sysadmin fixed server role, it fails unless a proxy account has been set up. A proxy account is a Windows account that a system administrator defines and sets a security context for within the Windows environment. When a user who is not a member of the sysadmin group runs xp_cmdshell, the commands are run within the security context of the defined proxy account.
The proxy account for xp_cmdshell can be created by executing sp_xp_cmdshell_proxy_account. The syntax of this command is as follows:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]
For example, the following command creates a proxy credential for the Windows domain user Developer\tom that has the Windows password ss2k5Unl:
sp_xp_cmdshell_proxy_account 'Developer/tom' , 'ss2k5Unl'
If NULL is passed as account_name, the proxy credential is deleted.
Because of the potential havoc that could be wreaked on your database server if xp_cmdshell got into the wrong hands, it is recommended that the capability to run xp_cmdshell be left disabled. If you must use xp_cmdshell, be very careful about who has access to it by limiting it to only those with sysadmin permissions if at all possible. If for some reason xp_cmdshell must be made available to all users, be sure that the permissions granted to the proxy account are restricted to the minimum permissions required to perform the commands that need to be invoked via xp_cmdshell.Bottom of Form
|
|
|
|
