t0nydean

INSERT INTO wait_type_description VALUES('ABR','Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.')
INSERT INTO wait_type_description VALUES('ASSEMBLY_LOAD','Occurs during exclusive access to assembly loading.')
INSERT INTO wait_type_description VALUES('ASYNC_DISKPOOL_LOCK','Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.')
INSERT INTO wait_type_description VALUES('ASYNC_IO_COMPLETION','Occurs when a task is waiting for I/Os to finish.')
INSERT INTO wait_type_description VALUES('ASYNC_NETWORK_IO','Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.')
INSERT INTO wait_type_description VALUES('AUDIT_GROUPCACHE_LOCK','Occurs when there is a wait on a lock that controls access to a special cache. The cache contains information about which audits are being used to audit each audit action group.')
INSERT INTO 

t0nydean

/*
Created by SQLWorks Consulting 2012
http://SQLWorks.blogspot.com
SQLWorkers@gmail.com
*/

CREATE PROCEDURE usp_Search @search_str varchar(1000)
AS
SET NOCOUNT ON

t0nydean

DECLARE @db_nm varchar(256), @sql_str varchar(1000), @search_str varchar(1000), @counter int

-- use temp table to store results
IF EXISTS (SELECT object_id from tempdb.sys.all_objects where name like '#search_results%') DROP TABLE #search_results
CREATE TABLE #search_results
(
	database_nm varchar(256),
	Object_nm varchar(256),
	object_type varchar(256),
	Column_nm varchar(256),

t0nydean

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

t0nydean

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

t0nydean

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)

t0nydean

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),