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.

	DECLARE @db_nm varchar(256), @sql_str varchar(1000), @search_str varchar(1000)
	SELECT @search_str = 'tablename'
	DECLARE curDB
	CURSOR FOR
	SELECT name
	FROM master.sys.databases
	OPEN curDB
	FETCH NEXT FROM curDB INTO @db_nm
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	SELECT @sql_str = 'SELECT distinct db_nm='''+@db_nm+''', OBJECT_NAME=o.name FROM '+@db_nm+'..syscomments s (NOLOCK)
	JOIN '+@db_nm+'..sysobjects o (NOLOCK) ON s.id = o.id
	WHERE text LIKE ''%'+@search_str+'%''
	SELECT TABLE_CATALOG as ''DATABASE'', TABLE_NAME, COLUMN_NAME
	FROM '+@db_nm+'.INFORMATION_SCHEMA.COLUMNS
	WHERE COLUMN_NAME LIKE ''%'+@search_str+'%'' or TABLE_NAME LIKE ''%'+@search_str+'%'''
	EXEC (@sql_str)
	FETCH NEXT FROM curDB INTO @db_nm
	END
	CLOSE curDB
	DEALLOCATE curDB

view raw early_stage_search_script.sql hosted with ❤ by GitHub

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:

	SELECT @sql_str = ' SELECT j.name, s.step_id, s.step_name, s.command
	FROM MSDB..sysjobsteps s JOIN MSDB..sysjobs j ON s.job_id = j.job_id
	WHERE s.command LIKE ''%'+@search_str+'%'''
	EXEC (@sql_str)

view raw sql_agent_job_search.sql hosted with ❤ by GitHub

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:

	CURSOR FOR
	SELECT name
	FROM master.sys.databases
	GROUP BY name HAVING HAS_DBACCESS(name) = 1 --funtion checks your access to each datbase

view raw dbaccess_checker.sql hosted with ❤ by GitHub

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:

	DECLARE @db_nm varchar(256), @sql_str varchar(1000), @search_str varchar(1000)
	-- use temp table to store results
	IF EXISTS (SELECT OBJECT_ID('temp_db.dbo.##search_results')) DROP TABLE ##search_results
	CREATE TABLE ##search_results
	(
	database_nm varchar(256),
	Procedure_nm varchar(256),
	Table_nm varchar(256),
	Column_nm varchar(256),
	Job_nm varchar(256),
	Step_id varchar(256),
	Step_nm varchar(256),
	command_text varchar(256)
	)
	SELECT @search_str = 'tablename'
	DECLARE curDB
	CURSOR FOR
	SELECT name
	FROM master.sys.databases
	GROUP BY name HAVING HAS_DBACCESS(name) = 1
	OPEN curDB
	FETCH NEXT FROM curDB INTO @db_nm
	WHILE (@@FETCH_STATUS <> -1)
	BEGIN
	--SELECT @db_nm --removed, redundant
	-- add code here to insert results into new temp table
	SELECT @sql_str = 'INSERT INTO ##search_results(database_nm, Procedure_nm) SELECT distinct db_nm='''+@db_nm+''', OBJECT_NAME=o.name FROM '+@db_nm+'..syscomments s (NOLOCK)
	JOIN '+@db_nm+'..sysobjects o (NOLOCK) ON s.id = o.id
	WHERE text LIKE ''%'+@search_str+'%''
	INSERT INTO ##search_results(database_nm, Table_nm, Column_nm) SELECT TABLE_CATALOG as ''DATABASE'', TABLE_NAME, COLUMN_NAME
	FROM '+@db_nm+'.INFORMATION_SCHEMA.COLUMNS
	WHERE COLUMN_NAME LIKE ''%'+@search_str+'%'' or TABLE_NAME LIKE ''%'+@search_str+'%'''
	EXEC (@sql_str)
	FETCH NEXT FROM curDB INTO @db_nm
	END
	CLOSE curDB
	DEALLOCATE curDB
	SELECT @sql_str = 'INSERT INTO ##search_results(Job_nm, step_id, step_nm, command_text) SELECT j.name, s.step_id, s.step_name, s.command
	FROM MSDB..sysjobsteps s JOIN MSDB..sysjobs j ON s.job_id = j.job_id
	WHERE s.command LIKE ''%'+@search_str+'%'''
	EXEC (@sql_str)
	SELECT *
	FROM ##search_results

view raw String_reference.sql hosted with ❤ by GitHub

PART 2 - Improvements

Integrating Sharepoint List data into SQL Server via SSIS Package

Have you ever wanted to be able to query your SharePoint lists from SQL Server? 

This seems to be a fairly common request so I wanted to demonstrate how to best accomplish this, this post was created using Visual Studio 2010 on a computer running SQL Server 2012, but will work back to SQL Server 2005. So by default your list of sources and destinations in an SSIS data flow task looks something like this, with variations between the versions. Notably absent is SharePoint...

So to remedy this, you COULD use a script task to write a web service call in a .NET language that pulls information from the SharePoint web service, if you have access and your SharePoint admin set it up correctly, however there is a much easier way. The SharePoint List adapter add-in for SSIS, available here on CodePlex . This is open source, so getting your IT dept to allow its use in the enterprise can take some work, but it is worth it. After a very quick install (follow the wizard...), your new source and destination options will include SharePoint Lists as shown below. I am not going into all the details of how to install this, codeplex covers all that already. If you have problems leave a comment and I'll try to help. MSDN agrees, this is the best way.

This is the result, just drag and drop these like any other source or destination in a data flow task. I have added some very helpful red arrows to illustrate whats going on here
.

jump to next post on using your list's GUID to avoid name change issues

UPDATE 10/17/2013

In yesterday's SQL PASS live stream Matt Masson mentioned in the SSIS talk that this SharePoint List adapter is the most popular codeplex item! So everyone who has been using this, you're doing it right. Until they release the OData source for SSIS and this is no longer needed of course...

Setting up your home network with two routers

If you live in a house that is two or three stories (like mine) you will probably at some point want to add a second wireless access point to your existing wireless modem/router to extend the WiFi coverage into the third floor or into the garage. I have recently undertaken this project and learned a few things...

1. Check if your second router has a wireless repeater function - THIS WILL BE VERY HELPFUL
Here is the full explanation right form the source: NetGear instructions . If your router has this function, just follow the steps for your brand/make fo router and you are good to go.

2. If your router does not have a 'repeater' or WDS function, you can still use it as an access point, follow these steps:

• Set the main router to act as DHCP server and make note of the IP range it will use for that function
• Set the second router to NOT act as a DHCP server
• Set second router to a static IP outside the range of IP that the main router will be using
• OPTIONAL - set the SSID of both routers to the same name and password so your devices can switch between them without losing connectivity
• Connect the main router to the second router via cat5 cable, but use a LAN port on them both, do not use the "internet" port on the second router.
• Finally set the channel of each router to something other than auto, make sure they are different so they don't interfere with each other.

This should work in almost all cases, if it doesn't add a comment and I'll see if I can help.

Error 1406 installing Office Pro Plus 2010

After a recent computer upgrade I was faced with re-installing the Office 2010 Pro Plus suite, which I had the discs for and assumed would be a no brainer. Upon happily inserting the CD and expecting to hit 'GO' and grab a snickers I was suddenly faced with this:

ERROR 1406: Setup cannot write the value Assembly to the registry key

After some reading about how I did not have sufficient permissions to alter my registry, and knowing this was not true, I tried a theory and disabled my anti-virus software (AdAware AntiVirus free edition) and the installation worked without an issue. I have also read references to WebRoot SpySweeper blocking registry changes as well, so if you are facing this same problem, before you go changing registry permissions, try disabling your anti-virus software, it may just work. Don't forget to turn it back on...

If you found this useful, please hit the +1, it's free!