SQL 2000 DBA Toolkit: Part Four - Additional Functionality

"Formula for success: Rise early, work hard, strike oil." - John Paul Getty

Introduction

Download the DBA Toolkit Here

In Part One of this series, we discussed the encryption tools in the DBA Toolkit. In Part Two we discussed regular expressions. Part Three covered phonetic matching. In this fourth installment, I will be covering the additional tools that didn't quite fit into the other categories.

As always, feel free to use these tools as you see fit. If you find them useful, please drop me a line and let me know which ones you use, how you use them, and what you find most useful.

Enjoy.

Click here to read Part One (Encryption), Part Two (Regular Expressions), or Part Three (Phonetic Matching)

Additional Functionality

So far we've covered most of the functionality in the SQL 2000 DBA Toolkit. In this article I'll describe additional functions that: 1) don't fit into the previously listed categories, and 2) are't extensive enough to deserve their own categories.

Installation

The installation instructions are in Part One of this series. To install, copy the DLL's to your MSSQL\BINN directory and run the INSTALL.SQL script. Installation troubleshooting steps are covered in the README.RTF file included in the \DOCS directory.

Additional Tools

xp_dirscan @path, @mask

The xp_dirscan extended procedure performs a recursive directory scan, and returns the names of all files that match the filemask supplied in the @mask parameter. The parameters supplied are:

• @path is the starting file path. All sub-directories under this file path are searched.
• @mask is the file mask. It can contain standard Windows file mask wild-card characters ("*" and "?").

The results are returned in a table with 7 columns:

• RowNum is an INTEGER column with a unique ID number for each row.
• Level is an INTEGER column indicating the level at which the file/sub-directory resides. The @path specified is the top level (Level 1).
• IsDir is a CHAR column containing a 'Y' indicating a sub-directory, or 'N' indicating a file.
• FullPath is a VARCHAR column containing the full pathname to the sub-directory of file.
• FileName is a VARCHAR column containing the file name for a file, or blank for a sub-directory.
• Size is a BIGINT column containing the size of the file, or the sum of all subordinate file sizes for a directory.
• FileCount is an INTEGER column containing a '1' for individual files, and a total count for all subordinate files/subdirectories for a given directory.

Note that all sub-directory names are returned in the table, regardless of the file mask settings. The file mask settings are used to limit the file names returned.

Fig. 1. Sample xp_dirscan results.

fn_encode_base64(@plaintext) fn_decode_base64(@enctext)

The fn_encode_base64 and fn_decode_base64 functions encode and decode a string to, or from, Base64 format. The parameters passed into the Base64 encoding/decoding functions and the results returned are VARCHAR format. Note that Base64 encoded data takes up 33% (or more) space than the equivalent non-Base64 encoded data.

Fig. 2. fn_encode_base64 and fn_decode_base64 sample results.

fn_hash_sha(@plaintext, @bits)

The fn_hash_sha function performs an SHA-2 type hash on the @plaintext passed in. The parameters are as follows:

• @plaintext is the plain text to hash. This parameter is a VARBINARY value.
• @bits is the number of bits to be returned in the hash. SHA-2 supports 256, 384 and 512 bit hash results.

The hash results are returned in VARBINARY format.

Fig. 3. Sample fn_hash_sha 256-bit hash result.

fn_propercase(@string)

The fn_propercase function takes an input string and 'proper-cases' it. I.e., the first letter of each word is capitalized and other characters are lowercased. Various punctuation marks, spaces and numbers are used to determine the beginning of words.

• @string is a VARCHAR value to be proper-cased. The result is returned as a VARCHAR.

Fig. 4. fn_propercase sample.

fn_rot13(@plaintext)

The fn_rot13 function accepts VARCHAR plain text and returns a ROT-13 enciphered VARCHAR result. Performing ROT-13 on a previously enciphered value returns the original value. I discussed ROT-13 in more detail in the previous Free Encryption Toolkit article.

Fig. 5. fn_rot13 sample

fn_CalculateHolidays(@region, @year)

The fn_CalculateHolidays function accepts a VARCHAR(2) region and an INTEGER year as parameters. It then calculates holidays for that region and returns them in the form of a table. Currently only 'US' (United States) and 'EN' (England/Wales) are recognized by the function. And only the dates of federal holidays (and a couple of other widely recognized religious holidays) are calculated. No state or local holidays are calculated.

This UDF can be easily extended to include other regions, states, or religious holidays by using the current code as a template. This function relies on a couple of other included functions: fn_modulo which performs a true modulo function on two numbers, fn_DoW_Occurrence which calculates the date of a particular occurrence of the Day of Week (i.e., the third Sunday in May), and fn_CalculateEaster which calculates Easter Sunday for any year between 1900 and 2099. All of these additional functions are described in greater detail in the DBATOOLKIT.CHM Help File.

Fig. 6. fn_CalculateHolidays sample

DBA Toolkit (Additional Functions) Quick Reference

Summary

This is the end of the fourth part of this series on the DBA Toolkit. In this article we talked about the additional tools included in the toolkit.

Download the DBA Toolkit Here