Sql Server数据库全文搜索脚本

----------------------------------------------------------------------------------------
--How to find the table and column name in the db that contains a certain record value--
----------------------------------------------------------------------------------------
   --DROP TABLE #searchresults
     
   DECLARE @SearchStr nvarchar(100)
   
   SET @SearchStr = 'PORECEIPTIAS'
   
   CREATE TABLE #searchresults (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',  'numeric','decimal', 'double', 'money')   
                           AND   QUOTENAME(COLUMN_NAME) > @ColumnName   
               )   
               IF @ColumnName IS NOT NULL   
               BEGIN   
                     INSERT INTO #searchresults   
                     EXEC    
                    (   
                           'SELECT ''' + @TableName + '.' + @ColumnName +  ''', LEFT(' + @ColumnName + ', 3630)    
                           FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                   	)   
               END   
         END      
   END    
   select * from #searchresults