TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
C# Corner
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
How to Search Text in Sql Server Database
Khan Abrar Ahmed
Jun 10
2015
Code
889
0
2
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
declare
@SearchStr
varchar
(
max
)=
'File'
declare
@Results
as
table
(
ColumnName
varchar
(
max
),
ColumnValue
varchar
(
max
)
)
SET NOCOUNT ON
DECLARE
@TableName
varchar
(
max
),
@ColumnName
varchar
(
max
),
@SearchStr2
varchar
(
max
)
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'
)
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
SQL Server
Search text in sql
SQL