|
Hey,
This is awesome, I've been looking for something like that, thank you
Well my questions are:
What is DNU, and why it shouldn't be in the results ?
And also what is (NOLOCK) ?
I've made some changes to your SP, so it'll work for only one db and doesn't create a base table
USE [YOUR_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spr_View_SearchAllObject]
@StringToSearch NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON
SET @StringToSearch = '%' + @StringToSearch + '%'
IF OBJECT_ID (N'#TB_TEMP_TABLESTORE', N'U') IS NULL
BEGIN
CREATE TABLE #TB_TEMP_TABLESTORE
(
[SEARCH_STRING] [nvarchar](128) NULL,
[DB_NAME] [nvarchar](128) NULL,
[OBJECT_NAME] [nvarchar](128) NULL,
[OBJECT_TYPE] [nvarchar](50) NULL
) ON [PRIMARY]
END
ELSE
BEGIN
DELETE FROM #TB_TEMP_TABLESTORE
END
INSERT INTO #TB_TEMP_TABLESTORE ([SEARCH_STRING] ,[DB_NAME] ,[OBJECT_NAME] ,[OBJECT_TYPE])
SELECT Distinct @StringToSearch AS [SEARCH_STRING], DB_NAME() as [DB_NAME], SO.Name AS [OBJECT_NAME], SO.[Type] AS [OBJECT_TYPE]
FROM sysobjects SO (NOLOCK)
WHERE [name] not like '%DNU%'
AND
(
SO.Name LIKE @StringToSearch
OR
EXISTS (
SELECT *
FROM syscomments SC (NOLOCK)
WHERE SO.Id = SC.ID
AND SC.Text LIKE @StringToSearch
)
)
ORDER BY SO.Name
SELECT * FROM #TB_TEMP_TABLESTORE
DELETE FROM #TB_TEMP_TABLESTORE
END
UPDATE:
We don't need the table if we are working on 1 DB
USE [YOUR_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spr_View_SearchAllObject]
@StringToSearch NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON
SELECT Distinct @StringToSearch AS [SEARCH_STRING], DB_NAME() as [DB_NAME], SO.Name AS [OBJECT_NAME], SO.[Type] AS [OBJECT_TYPE]
FROM sysobjects SO (NOLOCK)
WHERE [name] not like '%DNU%'
AND
(
SO.Name LIKE @StringToSearch
OR
EXISTS (
SELECT *
FROM syscomments SC (NOLOCK)
WHERE SO.Id = SC.ID
AND SC.Text LIKE ('%' + @StringToSearch+ '%')
)
)
ORDER BY SO.Name
END
UPDATE 2:
Find using list of SearchWords
USE [YOUR_DATABASE]
Go
DECLARE @Strings TABLE (String NVARCHAR(250))
--DECLARE @Separator NVARCHAR(1) = ' ', @STRs NVARCHAR(500) = 'Bills PaymentDate=' ------ The Idea is to replace every space with UNION ALL SELECT to seperate between all parts, then select all values respectively in one one big select statment
--SET @STRs = ' SELECT ''' + REPLACE(@STRs, @separator, ''' UNION ALL SELECT ''') + ''' ' -- Use UNION for DISTINCT values
--INSERT INTO @Strings EXEC(@STRs) -- INSERT INTO @Strings Table SELECT * Values UNION(ed) Together
INSERT INTO @Strings SELECT 'Bills' UNION SELECT 'PaymentDate = ' --UNION SELECT 'SET'
DECLARE @MinCount INT = NULL --NULL = Count
IF(@MinCount IS NULL) BEGIN SET @MinCount = (SELECT COUNT(String) FROM @Strings) END
UPDATE S SET S.String = '%' + S.String + '%' FROM @Strings S
SELECT Distinct DB_NAME() AS [DB_NAME], SO.Name AS [OBJECT_NAME], SO.[Type] AS [OBJECT_TYPE]
FROM SYSOBJECTS SO (NOLOCK)
WHERE [NAME] NOT LIKE '%DNU%'
AND (SELECT COUNT(String) FROM @Strings WHERE SO.name LIKE String) >= @MinCount -- >= @Count-1 -- = @Count -- > 0
OR EXISTS (SELECT *
FROM SYSCOMMENTS SC (NOLOCK)
WHERE SO.ID = SC.ID AND (SELECT COUNT(String) FROM @Strings WHERE SC.Text LIKE String) >= @MinCount) -- >= @Count-1 -- = @Count -- > 0 --AND SO.Type = 'P'
ORDER BY SO.Name
GO
|
|
|
|