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

Last Row of Table in SQL

Hi, Below is code which gives last row of table

SELECT TOP 1 * FROM TABLENAME
ORDER BY SOMEFIELD desc

How to find execution time of stored

Enable CLR integration in SQL 2005

Hello Friends,

Please folloe this liank and find how you can enable CLR integration in SQL Server 2005.

http://www.sqlclr.net/Articles/tabid/54/articleType/ArticleView/articleId/1/Enabling-CLR-in-SQL-Server.aspx

SQL Server : User Define Data type

Hello,

SQL Server 2005 and 2008 allow user define data type. This data type allow your own rule.

Please read below article for more information.

http://msdn.microsoft.com/en-us/library/ms190232.aspx

%d bloggers like this: