Search for text in Database

Question

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

Sonu Gupta · Answer

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

Rajanikant Hawaldar · Answer

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

Mageshwaran R · Answer

Hi, Try to refer below links: https://www.c-sharpcorner.com/UploadFile/rohatash/getting-the-name-of-the-server-and-databases-in-sql-server/

Sourav Mukherjee · Answer

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]%'

Code Alone · Answer

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

Jenith Thakkar · Answer

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

Sreekanth Reddy · Answer

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_'

Rakesh Singh · Answer

Hello, if u have to search particular column name(string,number) or else write query select * from table where column name like '%Rakesh'

tejal bhesaniya · Answer

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

Sudhakar Chaudhary · Answer

You Can Use Full Text Searching. http://www.c-sharpcorner.com/UploadFile/raj1979/how-to-use-full-text-search-in-sql-server-2008/

Chetan Kumar · Answer

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%'

Search for text in Database

Answers (11)