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.|
|Stored procedure basics||Return to Table of Contents|
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.
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.
Peruse our complete collection of tips on both stored procedures and undocumented stored procedures here.
|Undocumented stored procedures||Return to Table of Contents|
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.
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.
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.
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.
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.
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.
|More undocumented tricks||Return to Table of Contents|
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.
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.
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.
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
This was first published in September 2005