How to search all columns of all tables in a database for a keyword?

I was looking to create a such search where i can give simple one text box and get search in all columns of perticular table.

When i was looking for that i find this script which is really cool…

Find Origianl Article At. http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Here is stored procedure “SearchAllTables”. It accepts a search string as input parameter, goes and searches all char, varchar, nchar, nvarchar columns of all tables (only user created tables. System tables are excluded), owned by all users in the current database. Feel free to extend this procedure to search other datatypes.

The output of this stored procedure contains two columns:

– 1) The table name and column name in which the search string was found

– 2) The actual content/value of the column (Only the first 3630 characters are displayed)

Create this procedure in the required database and here is how you run it:

–To search all columns of all tables in Pubs database for the keyword “Computer”

EXEC SearchAllTables 'Computer'

GO

Here is the complete stored procedure code:

 

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

AS

BEGIN

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’)

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

END

Advertisements

About Asif
Hello Friends… I have done my Bachelor of Engineering in Information Technology from Gujarat University. I have 1.7 years experience of web and windows based application development.. My basic idea to start a blog is to store all important link which help me in my application and access to every where. Also this information is help others who want to makes their application powerful. I am interested in .net framework (1.0,2.0,3.0,3.5 and more). This blog contain also many information related to SQL, Custom Controls in C#.net Please share your ideas and any query if you have...

One Response to How to search all columns of all tables in a database for a keyword?

  1. Thanks for the info! I am recording this now at my site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: