Tuesday 31 July 2018

findInName - Stored Procedure to search all object names

-search all databases
CREATE OR ALTER PROC tools.findInName
(@searchstring VARCHAR(200))
AS
BEGIN
SET NOCOUNT ON
DECLARE @results TABLE (
    [search_term] VARCHAR(255),
    [database_name] VARCHAR(255),
    [schema_name] VARCHAR(255),
    [object_Name] VARCHAR(255),
 [type_desc] VARCHAR(255) )

DECLARE @dbname sysname;
DECLARE @actualsearchstring VARCHAR(200);

SET @actualsearchstring = REPLACE(@searchstring,'_','/_')

DECLARE @dsql nvarchar(max) = '
SELECT  ''' +@searchstring + '''  AS [search_term]
     ,DB_NAME() AS [database_name]
  ,TABLE_SCHEMA
  ,TABLE_NAME
  ,TABLE_TYPE 
 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE ''%'+@actualsearchstring+'%'' ESCAPE ''/''

UNION ALL

SELECT   ''' +@searchstring + '''  AS [search_term]
     ,DB_NAME() AS [database_name]
     ,ROUTINE_SCHEMA
  ,ROUTINE_NAME
  ,ROUTINE_TYPE 
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_NAME LIKE ''%'+@actualsearchstring+'%'' ESCAPE ''/''

';
DECLARE @execCommand nvarchar(max);

DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY 
FOR
SELECT name FROM sys.databases (NOLOCK)
OPEN dbs;
FETCH NEXT FROM dbs INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @execCommand = 'EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @dsql';

    INSERT @results
    EXEC sys.sp_executesql @execCommand, N'@dsql nvarchar(max)', @dsql

    FETCH NEXT FROM dbs INTO @dbname; 
END

CLOSE dbs;
DEALLOCATE dbs;
SELECT * FROM @results
END
GO

EXEC tools.findInName '_'

No comments: