Searching Across SQL Server Part 3

To give other users access to use your SQL search script, but keep it centrally managed and compiled on your server, you need to implement it as a stored procedure or function. The choice between stored procedure and function for most installations is largely personal preference, I chose to go with a stored procedure, if you're interested in the relative merits of each there is some good reading here:

I have placed the code for the full stored procedure on git hub here: GitHub Full Procedure Code
I will only display the changes I made in the interest of keeping this post from filling your screen with code.
/*
Created by SQLWorks blog 2012
http://SQLWorks.blogspot.com
SQLWorkers@gmail.com
*/ 

CREATE PROCEDURE Usp_search @search_str VARCHAR(1000)
AS
    SET nocount ON 

...

I added the CREATE statements in the beginning, and made a couple of tweaks to the dynamic SQL in order to prevent duplicate results, see comments in the full code for details.

The usage is different of course, now you will use EXECUTE to run your new procedure and pass in the string you are searching for as a parameter as shown below.

EXECUTE Usp_search 'Employees' 


Which yields the following results showing this keyword is present in two object names:

Let's pass in a keyword present in column names:

EXECUTE Usp_search 'EmployeeID' 

The results yielded are shown below:

Now you can incorporate this procedure into jobs or other processes on your database server or in your applications, or base a report on it to allow users to search for items in your databases as well.

Part 4 coming up next, creating an SSRS report around this procedure

Go back to Part 1
Go back to Part 2

Searching across your SQL Server Part 2

Click here for the first part of this post

As a continuation of my previous post on searching your SQL Server databases, below is a new and more efficient version of the search script. It allows you to customize the output in the SELECT statement from the results table before you run it to save extra querying after the results are created. The cursor has been eliminated in favor of a WHILE loop, and this could easily be converted to a stored procedure if you wanted to have it live on your server and be called by an application or other users. Of course there are probably small improvements that could be made, maybe I will explore some in the future, but you came here to get back to work and this script works.




Continue reading Part 3 - Converting this script into a stored procedure

Searching SQL Server for a word or phrase across all objects

So you know what the column is called, or what the table is called, but you just don't know where to find it on your SQL Server? This is my solution for searching throughout SQL Server for a particular string, it will run on 2005, 2008R2 and 2012.

First we need to define some variable and establish where we are going to look. The below code gets the variables we need created, and defines a cursor that will gather the names of all the databases on the server.
Then we need to cycle through these databases and do our search on each one. The following dynamic SQL iterates through the databases and performs the search on each.



And there you have it, but how can we improve on this? by eliminating the cursor? by using a table (temporary, table variable or CTE) to store our result set? Yes of course, but this works and will get you on your way. As an extra result, lets add a search that checks SQL Agent jobs as well, see below for that code:



So now you will get many different result sets, and if you have more than 100 databases on your server you will exceed the maximum result sets that SSMS will display in 2008R2, so what to do? Lets store the results in a table and display them with a SELECT statement, by doing this we can also eliminate the empty result sets. Working fine? If you're getting access errors one small change will address that, lets only look in the databases you actually have access to by adding this:



So this is all you need to search the text of procedures, views, triggers etc and the names of all tables and columns for a specific string. It works and you can use it now, BUT, there are major improvements to be made...next post will cover converting it to a stored procedure and using parameters, as well as better filtering of the output.

Here is the final version, with the temporary table and all other changes added:



PART 2 - Improvements