Tag Archives: search

MS SQL: Search keyword in the whole database

16 Dec

How to search for a keyword in the whole database. You need to do the following:

1: Create a procedure

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

2: Run the procedure
exec SearchAllTables 'Searchstring'
GO

Read more:
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Gmail – use of search criteria

5 Mar

Some useful search criteria:

  • has:nouserlabels
  • is:unread
  • is:read

 

Another article which is useful

http://raisedbyturtles.org/view-unlabeled-gmail/

http://blog.gnu-designs.com/howto-search-and-destroy-unlabeled-mail-in-google-gmail/

 

Slow search in MS SQL 2008

30 Nov

When experiencing slow search in our database, we did the following to solve it (slower in production system compared to test):

Alternative 1:

  1. Use sql server profile to check which sql is slow (check reads,writes,duration)
    • use column filter with applicationname and loginname
    • start
    • perform the search
    • stop
    • save to xml and trc for later – with xml easier to search
  2. Compare table content between two databases
  3. Check index
    • USE database
      go
      select * from sys.indexes where name like ‘indexname%’
      go
  4. Compare indexes between two databases
  5. Check last rebuild of indexes
    • USE database;
      GO
      SELECT name AS Stats,
      STATS_DATE(object_id, stats_id) AS LastStatsUpdate
      FROM sys.stats
      WHERE object_id = OBJECT_ID(‘table’)
      and left(name,4)!=’_WA_’;
      GO
  6. Rebuild all indexes
  7. Reorganise all indexes

Alternative 2:

  1. Use sql server profile to check which sql is slow (check reads,writes,duration)
    • use column filter with applicationname and loginname
    • start
    • perform the search
    • stop
    • save trc
  2. Use database engine tuning advisor
    • file – new session
    • workload: use trc-file above and database
    • choose which database to run tuning
    • start analysis
    • remember to save recommendations for later
    • apply the recommendations when you have looked through the recommendations
  3. perform the search again
    • if it’s still slow run through 1-2 until the performance is satisfactory

http://stackoverflow.com/questions/581592/do-all-sql-server-versions-rebuild-indexes-automatically-or-have-a-default-rebui

http://labs.episerver.com/en/Blogs/Per/Archive/2008/7/Automatic-index-rebuild-script/

http://stackoverflow.com/questions/2831293/tsql-know-when-index-rebuild-reorg-or-updatestatistics-was-last-run-on-sql-ser

http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/

http://technet.microsoft.com/en-us/library/ms189858(v=sql.105).aspx

http://technet.microsoft.com/en-us/library/ms187874(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms177500(v=sql.105).aspx