LabMice.net - The Windows 2000\XP\.NET Resource Index
Home | About Us | Search |

Last Updated: November 06, 2005



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. In this SearchSQLServer.com Fast Guide, 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 basics

  • 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

  • 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.


    More undocumented tricks

  • 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.



    DISCLAIMER: Our tips offer technical advice for enterprise IT professionals. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of Labmice.net and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.



    This site and its contents are Copyright 1999-2005 by LabMice.net. Microsoft, NT, BackOffice, MCSE, and Windows are registered trademarks of Microsoft Corporation. Microsoft Corporation in no way endorses or is affiliated with LabMice.net. The products referenced in this site are provided by parties other than LabMice.net. LabMice.net makes no representations regarding either the products or any information about the products. Any questions, complaints, or claims regarding the products must be directed to the appropriate manufacturer or vendor.