A stored procedure is a set of SQL statements stored under a procedure name to be executed as a group by your |
Requires Free Membership to View
| database server. It can be executed quickly inside your database server, saving you the time it would take to do the same work on a client. In part one of our stored procedure series of fast guides, we highlighted some particularly useful undocumented stored procedures. Here we take a broader look at some of SQL Server's handy stored procedures. |
TABLE OF CONTENTS
Basics
Data migration
Performance
Query and operations
  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.
| Data migration | Return to Table of Contents |
Restoring the database after migration is the easy part, but you may be left with mismatched logins and database users. The stored procedures in this tip will help you sort out your mismatches.
Learn how a stored procedure can be used to import data from other types of database's and text files to SQL Server.
It is possible to have a stored procedure in one database look at tables in another. Development expert Andrew Novick tells you how in this expert response.
| Performance | Return to Table of Contents |
Just a few small changes to a stored procedure could change your query performance. Performance and Tuning expert Jeremy Kadlec discusses ways to increase query performance by making alterations to your indexes instead of your stored procedures.
Stored procedures are a great way to optimize operations, so transactional processing in SQL Server is best done through a stored procedure. In this tip, contributor Serdar Yegulalp discusses optimizing transactional behavior in a stored procedure.
| Query and operations | Return to Table of Contents |
The sp_FindString routine allows you to search for occurrences of a string in the character columns (char, nchar, varchar, nvarchar) of a set of tables. The output includes the table name, column name and number of rows in which the string was found.
This simple routine is handy for finding columns by name and listing the table(s) in which they exist. The listing includes table name, column name, ordinal position, data type, data size, width/precision and scale.
Brian Walker presents a simple stored procedure to return a list of all the objects of selected types in the current database.
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.
Click for the first part of our series: Fast Guide: Undocumented stored procedures
This was first published in September 2005
Join the conversationComment
Share
Comments
Results
Contribute to the conversation