Find Column in All Databases

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 began with having to locate each of these columns in order to determine the impact of the change, and what actually needed to change. For this, I used the undocumented sp_MSForEachDB procedure. It is a great procedure that actually takes a SQL string as an input parameter. The SQL used for the input parameter must then contain a question mark (?) in which each database replaces the question mark and the SQL is executed. Here is an example: [cc lang=”sql”] sp_MSForEachDB @command1=’USE ?; SELECT Table_Catalog ,Table_Schema ,Table_Name ,Column_Name ,Data_Type ,Character_Maximum_Length FROM INFORMATION_SCHEMA.COLUMNS WHERE Data_Type = ”int” AND COLUMN_NAME like ”%ColumnNameHere%”’ [/cc] To utilize correctly, replace the ColumnNameHere with the name of the column you want to find (or what the column name should be like). It will then return you all the columns on the server that are named like the column you specified. Continue reading ...

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 however, you simply need to pass the @job_name parameter to the sp_delete_job stored procedure. [cc lang=”sql”] IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N’jobnamegoeshere’) EXEC sp_delete_job @job_name = N’jobnamegoeshere’ , @delete_unused_schedule=1 [/cc]

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 The biggest way you can help SQL Server speed up your queries, is by helping it determine how to limit the result set as soon as possible. One of the biggest factors in this is in knowing the tables you are joining together. This translates to: How many records are in your tables? What are the best ways to join your tables? What are the existing indexes on the tables? Knowing the number of records in a table is very important. When joining tables using inner joins, you always want to join the smallest table first. Why you ask? Say you do not use a WHERE clause and you simply join three tables together. If you start your FROM clause with the largest table, then the first table SQL sees is a very large table, so it sets up an execution plan for a very large result set. Then let’s say the last of the 3 tables only has 10 records, by the time SQL visits that table it’s too late, the execution plan has been set to output 100,000 records, whereas if you specified the first table with 10 records after the FROM clause, […] Continue reading ...

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. [cc lang=”sql”] DECLARE @BaseString varchar(max) SET @BaseString = ‘Quick Brown Fox’ SELECT SUBSTRING ( @BaseString — The base string to extract from ,7 — Start Position ,5 — Length of Characters ) [/cc] The above query returns the value ‘Brown’. Note: This is equivalent to the Oracle function: substr