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
Search Text In Entire SQL Database
Arun Kumar
Jul 26
2016
Code
534
0
0
facebook
twitter
linkedIn
Reddit
WhatsApp
Email
Bookmark
expand
DECLARE
@Results
Table
(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
DECLARE
@TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET
@TableName =
''
SET
@SearchStr2 = QUOTENAME(
'%Winterization%'
,
''
''
)
WHILE @TableName
IS
NOT
NULL
BEGIN
SET
@ColumnName =
''
SET
@TableName =
(
SELECT
MIN
(QUOTENAME(TABLE_SCHEMA) +
'.'
+ UOTENAME(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 Query
Search Text in database