Occasionally, there comes a requirement to search all databases on a particular server for either columns with a specific name, or columns relating to a specific subject. In the most recent case, I had to find all similar columns in all databases because the company plans to change the datatype of these columns. My task [...]
Archive of entries posted on April 2010
Delete Job by Name
When scripting out jobs using ssms (sql server management studio) the default script for a drop statement is to drop the job according to it’s job_id. This is not beneficial however when pushing code to different environments. Job_id’s are specific to the windows environment in which they are created. To get around this is easy [...]
How to Write Optimized Stored Procedures
There are many factors to speeding up stored procedures. Knowing these in advance can save you and your company many hours of headaches. Using these practices when writing procedures, you can turn your SQL Server into a fine-tuned machine. This tutorial will cover the specifics to help you tune your procedures optimally. Know your tables [...]
SQL Server Substring
The substring function takes 3 arguments. The first argument is the actual string in which you want to extract from. The second argument is the start position, and the third argument is the number of characters to extract. DECLARE @BaseString varchar(max) SET @BaseString = ‘Quick Brown Fox’ SELECT SUBSTRING ( @BaseString — The base string [...]
SQL Server Contains Equivalent
Many Oracle developers trying to find the SQL Server function compatible with their Contains clause will most likely end up on this page. Therefore, this page title is directed towards the Oracle developer rather than for the SQL Server’s Contains function which is used for full-text searching. The most similar function to Oracle’s contains is [...]
Dynamic Numbers Table
Using a numbers table is helpful for many things. Like finding gaps in a supposed sequence of primary keys, or generating date ranges or any numerical range. In some cases, you will be in a production system that does not already contain a numbers table and you will also be unable to add one. In [...]
SQL Boolean
In SQL Server, boolean values can be represented using the bit datatype. Bit values differ from boolean values in that a bit can actually be one of three values 1, 0, or NULL; while booleans can only either be true or false. When assigning bits, it is best to use 1 or zero to indicate [...]
Extracting Data from a Source System to History Tables
This is a topic I haven’t found much information written about, however nearly every system I’ve worked with needs this exact functionality. It is important that the method for extracting data be done in a way that does not hinder performance of the source system. In this example, the goal is to extract data from [...]
