Search for text in Database

Mohamed

14y3.8k

Is there away to search for text in Database in Sql server

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sonu Gupta6y ago
Go to below links -

https://www.red-gate.com/simple-talk/sql/t-sql-programming/searching-for-strings-in-sql-server-databases/

https://stackoverflow.com/questions/9185871/how-to-search-sql-server-database-for-string
0
Rajanikant Hawaldar6y ago
https://stackoverflow.com/questions/9185871/how-to-search-sql-server-database-for-string https://www.codeproject.com/Questions/683413/How-to-search-a-string-or-text-in-entire-database
0
Mageshwaran R6y ago
Hi,
Try to refer below links:
https://www.c-sharpcorner.com/UploadFile/rohatash/getting-the-name-of-the-server-and-databases-in-sql-server/
0
Sourav Mukherjee7y ago
Hello, Use this easy script:

SELECT DISTINCT a.[name] FROM sysobjects a
INNER JOIN syscomments b on a.id = b.id
WHERE b.[text] LIKE '%[search-term]%'
0
Code Alone7y ago
Hope It will help you
DECLARE @SearchText NVARCHAR(100)='INSERT INTO TBL_ORDER_P(orderID,TDate) VALUES('
SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@SearchText+'%'
    ORDER BY 2,1
0
Jenith Thakkar7y ago
Execute this query with your search string and it will provide you output

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL

BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results
0
Sreekanth Reddy7y ago
Hi Mohamed,

For searching we needs to use the where clause.
If you know come partial data of a required data we needs to apply the wild card operatores for the where clause.
Wild card operators are "%"(percentage) & '_'(underscore).
What % says : anynumber of charracters from the placed position.
what _ says : one character for one under score in the placed position.
SELECT FROM table_name
WHERE column LIKE 'abc%'
or
SELECT FROM table_name
WHERE column LIKE '%abc%'
or
SELECT FROM table_name
WHERE column LIKE 'abc_'
or
SELECT FROM table_name
WHERE column LIKE '_abc'
or
SELECT FROM table_name
WHERE column LIKE '_abc_'

0
Rakesh Singh13y ago
Hello,

if u have to search particular column name(string,number) or else
write query
select * from table where column name like '%Rakesh'
0
tejal bhesaniya14y ago
DECLARE @Search varchar(255) SET @Search='Your Text For Search' SELECT DISTINCT o.name AS Object_Name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%'+@Search+'%' ORDER BY 2,1
0
Sudhakar Chaudhary14y ago
You Can Use Full Text Searching.

http://www.c-sharpcorner.com/UploadFile/raj1979/how-to-use-full-text-search-in-sql-server-2008/

0
Chetan Kumar14y ago
Hi,

U want to search content in column or text data type for tables.
u can use like condition if want search particular word or anythin

select * from tb1 where ename like '%shil%'
0