Fast Guide: Undocumented stored procedures

Stored procedures can help you control access, preserve data integrity and improve productivity, yet you may not be tapping into all the routines that ship with SQL Server. Here we highlight some particularly useful undocumented stored procedures, as well as some other tips and tricks not to miss.

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 basics  Return to Table of Contents

  • 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  Return to Table of Contents

  • 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  Return to Table of Contents

  • 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


  • This was first published in September 2005
    This Content Component encountered an error

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to:

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close