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;
