 |
 |
 |
Home > Fast Guide: Undocumented stored procedures |
 |
|
|
 |
 |
 |
Fast Guide: Undocumented stored procedures |
 |
| 13 Sep 2005 | SearchSQLServer.com |
 |


|
A stored procedure is a set of SQL statements stored in a database in compiled form so that it can be shared by numerous programs. They can help you control access to data, preserve data integrity and improve productivity, yet many SQL Server DBAs and developers fail to tap into all the undocumented goodies that ship with SQL Server. Here we highlight some particularly useful undocumented stored procedures, as well as some other undocumented tricks not to miss.
|
TABLE OF CONTENTS
Stored procedure basics
Undocumented stored procedures
More undocumented tricks
Stored procedure primer
This chapter from The Guru's Guide to SQL Server Stored Procedures, XML and HTML, by Ken Henderson serves to prime the discussion on SQL Server stored procedure programming.
Stored procedure expert technical advice
Get the stored procedure help you need in this topics section. You'll find expert advice on everything from using stored procedures to look at different databases to working with stored procedures in Crystal Reports.
Stored procedure tips
Peruse our complete collection of tips on both stored procedures and undocumented stored procedures here.
Undocumented stored procedures: Working directly with directories, files and drives
Encrypting image fields in SQL Server 2000 is possible, but not easy. Contributor Serdar Yegulalp explains how your encryption needs are better addressed in SQL Server 2005, and he offers some encryption resources to help you in the meantime.
Repair table indexes with sp_fixindex
This undocumented stored procedure in SQL Server 2000 can be used to repair a given system table index. Contributor Serdar Yegulalp explains how to use sp_fixindex to fix corrupted indexes and ensure a smooth-running SQL Server.
Return string type for a column with sp_gettypestring
This useful (albeit unsupported) stored procedure in SQL Server 2000 be used to return the type string for a column in a table. Contributor Serdar Yegulalp explains how to use sp_gettypestring.
View SQL Server 2000 raw data using DBCC PAGE
If you want to view and interpret raw data in your database, this undocumented command in SQL Server 2000 will enable you to dump data to the console or log. Contributor Serdar Yegulalp explains how to use the DBCC PAGE command.
Functions to manipulate the registry
Among the many undocumented functions you'll find in SQL Server 7.0 and 2000 is a set of extended stored procedures to obtain, set and delete Registry keys. Contributor Serdar Yegulalp explains when and how to use these procedures.
Find space occupied by all tables and indexes with sp_msforeachdb
If you're looking for a great capacity planning tool, look no further. Find out how to use the undocumented sp_msforeachdb routine to find the space occupied by all tables and indexes in a SQL Server 2000 database.
Stored procedures: Perform specified operations on tables and objects
To perform a particular operation on a specified set of tables or objects, contributor Brian Walker offers two simple and flexible stored procedures: sp_FixTables and sp_FixObjects. These routines allow you to generate T-SQL code, immediately execute commands, more easily specify a subsets and perform character substitutions. The sp_FixTables routine is similar to the undocumented stored procedure sp_MSforeachtable.
Documented and undocumented trace flags
Trace flags are valuable DBA tools that allow you to enable or disable a database function temporarily -- and most are undocumented in SQL Server 2000. Get a list of useful trace flags in this book excerpt from Chapter 5 of the book "SQL Server 2000 for experienced DBAs" written by Brian Knight.
Undocumented functions to encrypt column data
There is no documented facility for SQL Server column data encryption, but you could use perform some encryption tasks with the undocumented functions pwd_encrypt( ) and pwd_compare( ). Find out if you'd really want to in this expert response by site expert Kevin Kline.
Encrypting data in SQL Server: Dos and don'ts
Here is a list of steps you should and should not take to encrypt objects and data in SQL Server. It includes two undocumented functions that SQL Server uses to manage password hashing.
Do you have a favorite undocumented stored procedure? Let us know! We'll post it as a tip on SearchSQLServer.com and add it to the above list.
Click for the next part of our series: Fast Guide: Stored procedures
');
// -->

|
 |
|
 |
 |
 |
| TechTarget provides technology professionals with the information they need to perform their jobs - from developing strategy, to making cost-effective purchase decisions and managing their organizations' technology projects - with its network of . |
|
| |
All Rights Reserved, , TechTarget |
|
|
|
|
|