Readers' Tips and Tricks - 19 Oct 2000

Editor's Note: Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or fewer) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50.

Generating a Table Description

As a SQL Server DBA and Visual Basic (VB) developer, I often need a quick reference to SQL Server table structures, such as column names, data types, field lengths (mostly for character data types), and null status of fields. Microsoft doesn't recommend directly querying the system tables to get this information because system tables can change from release to release. In SQL Server 7.0, Microsoft introduced information schema views of system meta data about objects, permissions, and so on. (For more information about information schema views, see Kalen Delaney, Inside SQL Server, "Property Functions, Schema Views," April 2000.) But you can't access information schema views if you're using SQL Server 6.5. SQL Server 6.5 offers some system stored procedures that compile the kind of basic information I seek, but the procedures aren't well documented, and they yield more information than I need.

To support SQL Server 6.5, I pulled together code from several system stored procedures to create a stored procedure that returns a brief table structure. Listing 1 shows a SQL Server 6.5 version of the stored procedure, which I call sp_desc. Given a table name, the procedure queries the system tables systypes, syscolumns, and sysobjects for column name, data type, length in bytes (or in characters for string data types), and null status. The procedure determines null status by using a bitwise comparison operator. I added the cast statements for readability.

Listing 2 shows a SQL Server 7.0 version of the procedure, which is a little simpler because the syscolumns table in SQL Server 7.0 defines a nullability column. However, you could also use the SQL Server 6.5 version of the procedure.

Finally, Listing 3 shows a query that you can run against SQL Server 7.0's INFORMATION_SCHEMA.COLUMNS view to get results similar to those that the stored procedure returns. Notice two differences between this method and the stored procedure. First, the COLUMNS view expresses the data type length in one of two fields, depending on whether the data type is string or numeric. The CASE statement evaluates whether the data type is string or numeric, then returns the correct value in the appropriate field. Second, the COLUMNS view expresses the length for a numeric data type in maximum number of digits rather than in bytes. For example, the COLUMNS view would return the length of a floating point numeric data type as 53, whereas a direct query of the system tables would return a length of 8 bytes.

—Joe Bishop
[email protected]

Job Schedule Report

Imagine that at 4:50 pm on Friday, your boss pops into your office and asks for a list of all scheduled SQL Server jobs and their duration. When this happened to us, we had to double-click each job (in our case, more than 30 jobs) to access the Schedule tab, find the exact schedule for that job, then right-click the job name to view the job history. We then had to calculate the average duration of each job. This method might give you carpal-tunnel syndrome, but it won't help you maintain a good working relationship with your boss. So, we created stored procedure usp_Scheduled_Jobs, which returns information about scheduled jobs. Figure 1 shows a sample report from usp_Scheduled_Jobs.

The usp_Scheduled_Jobs stored procedure, which Web Listing 1 shows, gets its information from the sysjobs, sysjobschedules, and sysjobhistory system tables and from the sp_get_schedule_description system stored procedure. (You can view Web Listing 1 by entering InstantDoc ID 15560 at http://www.sqlmag.com/ and clicking Download the code in the Article Information box.) All the database objects reside in the msdb database. Usp_Scheduled_Jobs has four main parts. The first part is a temporary table called #dba_Scheduled_Jobs, which we use throughout the stored procedure. Table 1 describes the temporary table's columns, which represent key job information areas.

The usp_Scheduled_Jobs stored procedure then uses the INSERT INTO...SELECT statement to insert data into the temporary table. The stored procedure uses CASE to convert the data to a more readable format. For example, we paid special attention to how we formatted the start_time column and to how we calculated AVG_runtime. The sysjobschedules system table stores jobs' start times in military format, but we convert them to the am/pm format. In addition, sysjobschedules stores the run duration in a HHMMSS time format, so 50210 represents 5 hours, 2 minutes, and 10 seconds. To calculate the average runtime, we convert the hours and minutes into seconds.

Next, usp_Scheduled_Jobs uses SQL Server 7.0's sp_get_schedule_ description system stored procedure to retrieve the English description of when the job is scheduled to run. The usp_Scheduled_Jobs stored procedure uses a cursor to obtain all the parameters that we need to pass to sp_get_schedule_description. Depending on how sp_get_schedule_description returns the job information, our stored procedure formats the data for better readability.

Last, usp_Scheduled_Jobs uses a SELECT statement to return the scheduled job information that we need. In this part of the stored procedure, we limit the display length of the job name and description, which can both be very long. We format MAX_runtime with colons between the hours, minutes, and seconds to make the time more readable. And we convert the AVG_runtime from seconds to HH:MM:SS format.

Before you create and run usp_Scheduled_Jobs, remember that only jobs that have a history will show up on the report. In addition, if a job has several schedules, the average time will be the same for all the schedules.

—Jose L. Amado-Blanco
[email protected]
—and Jason Carter
[email protected]

Corrections to this Article: