October 2009 - How Tos

Sort by: Most Recent | Most Viewed | Most Commented

  • Create a system stored procedure

    A system stored procedure is a proc that is stored in the master database that you can run in the context of other databases on the instance. For example, sp_msforeachtable is an example of a system stored procedure. You can run this procedure in any database on the instance and it will loop through the tables in the database and issue a command against each. As an example, here is a stored procedure I wrote when SQL Server 2005 first came out. This procedure uses a dynamic management view to recurse...

  • run_duration in SQL Server system tables

    If you are interested in the run duration of the jobs on your SQL Server you would most likely query the system tables sysjobs and sysjobhistory from the msdb database in a manner similar to this: select name ,enabled ,run_status ,run_date ,run_time ,run_duration from msdb.dbo.sysjobs sj join msdb.dbo.sysjobhistory sjh on sj.job_id = sjh. What is interesting (and yes, it could be misleading!) is that the run_duration is presented by the following formula: (hours*10000) + (minutes*100) + seconds The...

  • sp_MSForEachDB - leashed and unleashed

    First things first - a few words on Microsoft undocumented procedures (and tools): it is this simple - USE THEM WITH CAUTION. There are several out there, and they are all great. If you compare sp_who (documented) with sp_who2 (UN-documented) you will see the advantages. In the case of sp_MSForEachDB (UN-documented), there are several warnings I would like to bring to your attention: 1. Do not abuse it, since it is a global cursor. 2. UN-documented means that if something goes wrong, you will not...
Copyright SQL Server Nation 2010
Powered by Community Server (Non-Commercial Edition), by Telligent Systems