SQL-Server-Tools/sp_Find.txt

CREATE PROCEDURE sp_Find
	@StoredProc VARCHAR(200)=''
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Loop INT
	DECLARE @Count INT
	DECLARE @Database VARCHAR(200)
	DECLARE @SQL NVARCHAR(4000)
	CREATE TABLE #Output
	(
		[Database] VARCHAR(200),
		[Item] VARCHAR(200),
		[Type] VARCHAR(50)
	)
    DECLARE @Databases TABLE
	(
		[DatabaseID] INT IDENTITY(1,1),
		[Database] VARCHAR(200)
	)

	IF LEN(ISNULL(@StoredProc,'')) = 0
	BEGIN
		DECLARE @Info TABLE
		(
			Info VARCHAR(100)
		)
		INSERT INTO @Info (Info) VALUES ('sp_Find {Item}')
		INSERT INTO @Info (Info) VALUES ('Searches all databases for a table name,')
		INSERT INTO @Info (Info) VALUES ('column name, or stored procedure name')
		INSERT INTO @Info (Info) VALUES (' containing {Item}')
		INSERT INTO @Info (Info) VALUES ('')
		INSERT INTO @Info (Info) VALUES ('Check out other cool tools like')
		INSERT INTO @Info (Info) VALUES ('sp_ColumnsInATable')
		INSERT INTO @Info (Info) VALUES ('sp_CreateUserDefinedType')
		INSERT INTO @Info (Info) VALUES ('sp_DropColumn')
		INSERT INTO @Info (Info) VALUES ('sp_FindColumn')
		INSERT INTO @Info (Info) VALUES ('sp_FindInProc')
		INSERT INTO @Info (Info) VALUES ('sp_FindInAllProcs')
		INSERT INTO @Info (Info) VALUES ('sp_List')		
		INSERT INTO @Info (Info) VALUES ('sp_varinsp')
						
		SELECT Info from @Info
		RETURN
	END

	INSERT INTO @Databases ([Database])
	SELECT name from sysdatabases 
    WHERE name NOT IN ('master','tempdb','model','msdb')
    AND has_dbaccess(name) = 1;

	SET @Count = @@ROWCOUNT
	SET @Loop = 1
	WHILE @Loop <= @Count
	BEGIN
		SELECT @Database = [Database]
		FROM @Databases
		WHERE DatabaseID = @Loop
		
		SET @SQL = N'INSERT INTO #Output([Database],Item,[Type]) SELECT ''' + @Database + ''',name, case xtype when ''u'' then ''Table'' when ''p'' then ''Procedure'' end from ' + @Database + '..sysobjects WHERE xtype in (''U'',''P'') AND NAME LIKE ''%' + @StoredProc + ''''
		EXEC sp_sqlexec @SQL

		SET @SQL = N'INSERT INTO #Output([Database],Item,[Type]) SELECT ''' + @Database + ''',so.name+''.''+sc.name, ''Column'' from ' + @Database + '..sysobjects as so inner join ' + @Database + '..syscolumns as sc on so.id = sc.id WHERE so.xtype = ''U'' AND sc.NAME LIKE ''%' + @StoredProc + ''''
		EXEC sp_sqlexec @SQL
		SET @Loop = @Loop + 1
	END

	SELECT [Database],Item,[Type]
	FROM #Output
	ORDER BY [Database],[Type],Item

	DROP TABLE #Output

END
GO