Monday, 26 June 2017

SQL Database Full Text Search

  1. DECLARE @SearchStrColumnName nvarchar(100), @SearchStrColumnValue nvarchar(100), @SearchStrInXML bit  
  2. SET @SearchStrColumnValue = '%OWNED%' /* use LIKE syntax */  
  3. SET @SearchStrColumnName = NULL /* NULL for all, use LIKE syntax */  
  4. SET @SearchStrInXML = 0 /* Searching XML data may be slow */  
  5.   
  6. IF OBJECT_ID('tempdb..#Results'IS NOT NULL DROP TABLE #Results 
  7.  
  8. CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))  
  9.   
  10. SET NOCOUNT ON  
  11.   
  12. DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)  

  13. SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')  

  14. DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))  
  15.   
  16. WHILE @TableName IS NOT NULL  
  17. BEGIN  
  18. SET @TableName =  
  19. (  
  20.    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))  
  21.    FROM INFORMATION_SCHEMA.TABLES  
  22.    WHERE TABLE_TYPE = 'BASE TABLE'  
  23.    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName  
  24.    AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0  
  25. )  
  26.   
  27. IF @TableName IS NOT NULL  
  28. BEGIN  
  29. DECLARE @sql VARCHAR(MAX)  
  30. SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE  
  31. FROM INFORMATION_SCHEMA.COLUMNS  
  32. WHERE TABLE_SCHEMA = PARSENAME(''' + @TableName + ''', 2)  
  33. AND TABLE_NAME = PARSENAME(''' + @TableName + ''', 1)  
  34. AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''bigint'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')  
  35. AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'  

  36. INSERT INTO @ColumnNameTable  
  37. EXEC (@sql)  
  38. WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)  
  39. BEGIN  
  40. SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable  
  41. SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @SearchStrInXML WHEN 1 THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + '''  
  42. FROM ' + @TableName + ' (NOLOCK) ' +  
  43. ' WHERE ' + CASE @SearchStrInXML WHEN 1 THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue  
  44. INSERT INTO #Results  
  45. EXEC(@sql)  
  46. DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName  
  47. END   
  48. END  
  49. END  

  50. SET NOCOUNT OFF  
  51. SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results  
  52. GROUP BY TableName, ColumnName, ColumnValue, ColumnType  

Output:


No comments:

Post a Comment

Resetting IDENTITY Seed in Table Variable

For a "normal" table, you would use:  DBCC  CHECKIDENT( ' [table_name]' , RESEED, [new_reseed_value]). you cannot reseed...