Search for an Object in all Databases

CREATE PROCEDURE [dbo].[SearchObject]
(
@SearchString VARCHAR(255) ,
@NotContains VARCHAR(255)
)
AS
DECLARE @Text VARCHAR(1500) ,
@TextInit VARCHAR(1500);

SET @TextInit = 'USE @Code

INSERT INTO ##Temp2

SELECT

''@Code'' AS dbName,

a.[Object Name],

a.[Object Type]

FROM (SELECT DISTINCT

sysobjects.name AS [Object Name],

CASE

WHEN sysobjects.xtype = ''C'' THEN ''CHECK constraint''

WHEN sysobjects.xtype = ''D'' THEN ''Default or DEFAULT constraint''

WHEN sysobjects.xtype = ''F'' THEN ''Foreign Key''

WHEN sysobjects.xtype = ''FN'' THEN ''Scalar function''

WHEN sysobjects.xtype = ''P'' THEN ''Stored Procedure''

WHEN sysobjects.xtype = ''PK'' THEN ''PRIMARY KEY constraint''

WHEN sysobjects.xtype = ''S'' THEN ''System table''

WHEN sysobjects.xtype = ''TF'' THEN ''Function''

WHEN sysobjects.xtype = ''TR'' THEN ''Trigger''

WHEN sysobjects.xtype = ''U'' THEN ''User table''

WHEN sysobjects.xtype = ''UQ'' THEN ''UNIQUE constraint''

WHEN sysobjects.xtype = ''V'' THEN ''View''

WHEN sysobjects.xtype = ''X'' THEN ''Extended stored procedure''

END AS [Object Type]

FROM sysobjects

WHERE sysobjects.type IN (''C'', ''D'', ''F'', ''FN'', ''P'', ''K'', ''S'', ''TF'', ''TR'', ''U'', ''V'', ''X'')

AND sysobjects.category = 0

AND CHARINDEX(''@SearchString'', sysobjects.name) > 0

AND ((CHARINDEX(''@NotContains'', sysobjects.name) = 0

–OR CHARINDEX(''@NotContains'', sysobjects.name) <> 0
))) a';

SET @TextInit = REPLACE(@TextInit, '@SearchString', @SearchString);

SET @TextInit = REPLACE(@TextInit, '@NotContains', @NotContains);

SELECT name AS dbName ,
CAST(NULL AS VARCHAR(255)) AS ObjectName ,
CAST(NULL AS VARCHAR(255)) AS ObjectType
INTO ##Temp1
FROM master.dbo.sysdatabases
ORDER BY name;

SELECT *
INTO ##Temp2
FROM ##Temp1
WHERE 1 = 0;

DECLARE @Code VARCHAR(255) ,
@Count INT ,
@Incrementer INT;

SET @Count = ( SELECT COUNT(dbName)
FROM ##Temp1
);

DECLARE c_k CURSOR LOCAL FAST_FORWARD
FOR
SELECT dbName
FROM ##Temp1
ORDER BY dbName DESC;

OPEN c_k;

FETCH NEXT FROM c_k INTO @Code;

SET @Incrementer = 1;

WHILE ( @@fetch_status = 0 )
AND ( @Incrementer <= @Count )
BEGIN

SET @Text = REPLACE(@TextInit, '@Code', @Code);

PRINT @Text;

EXEC (@Text);

SET @Incrementer = @Incrementer + 1;

DELETE FROM ##Temp1
WHERE dbName = @Code;

FETCH NEXT FROM c_k INTO @Code;

END;

CLOSE c_k;

DEALLOCATE c_k;

SELECT *
FROM ##Temp2
ORDER BY dbName ,
ObjectType;

DROP TABLE ##Temp2;

DROP TABLE ##Temp1;