FTI on whole Database

I am new at FULLTEXT INDEX, I wonder if there is a possibility to index the whole DB at once and search it in the same way rather than specifying the Table and the columns?

NO, and I can't imagine why one would consider that as a possibility.

I wouldn't consider searching datetime columns, or money columns, or numeric columns for a text string. So even if it were possible, it would amount to a terrible waste of time/effort searching unnecessary columns. As well as significant resources just to create and maintain indexes on columns that may never need to be searched.

|||

Ok, Here is the scenario, We have this huge DB from a client -about 6k Tables!!!!- I am looking for the column that contains a specific string? I thought of using the FULLTEXT INDEX to get all the instances that contains that strings.
Is there a way to do that? Definitely as you mentioned it will be a terrible waste of time/effort but I don't see doing this otherwise, searching the DB table by table will be worse.

|||

No matter how you cut it, that will be a resource intensive task.

Here is a procedure that I created that would do the search. Hopefully, it will give you a good idea or two. Expect it to take quite some time with 6k tables.

--*******************************************
-- Problem: Locate data in any column, any table
-- Demonstrates:
-- WHILE looping
-- Using Dynamic SQL (sp_executesql)
-- #Temp Table vs. Table Variable

--*******************************************

SET NOCOUNT ON

DECLARE
@.TotalRows int,
@.Counter int,
@.TableName varchar(50),
@.ColumnName varchar(50),
@.FieldValue varchar(250),
@.SQLCommand nvarchar(1000),
@.ValueToFind varchar(100)

-->
SET @.ValueToFind = 'mexico'
USE Northwind -- For demo only, not needed

-->

DECLARE @.MyTable table
( RowID int IDENTITY,
TableName varchar(50),
ColumnName varchar(50)
)

CREATE TABLE #FoundTable
( RowID int IDENTITY,
Tablename varchar(100)
)

INSERT INTO @.MyTable
SELECT
TABLE_NAME,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'char', 'varchar', 'nchar', 'nvarchar', 'text', 'ntext' )

SELECT
@.TotalRows = @.@.ROWCOUNT,
@.Counter = 1

WHILE ( @.Counter <= @.TotalRows )

BEGIN

SELECT
@.TableName = TableName,
@.ColumnName = ColumnName
FROM @.MyTable
WHERE RowID = @.Counter

SET @.SQLCommand = 'IF EXISTS ( '+
'SELECT 1 FROM [' + @.TableName + '] ' +

'WHERE [' + @.ColumnName + '] LIKE ''%' + @.ValueToFind + '%'' ' +
' )' +
'INSERT INTO #FoundTable ' +
' SELECT ''[' + @.TableName + ']([' + @.ColumnName + '])'''

EXECUTE sp_executesql @.SQLCommand

SET @.Counter = ( @.Counter + 1 )

END

SELECT * FROM #FoundTable

DROP TABLE #FoundTable

/*
RowID Tablename
-- --
1 Invoices(Salesperson)
2 Employees(LastName)
3 Employees(PhotoPath)
*/

|||Thanks!!!!!!!!!!! It worked, I had some issue with the variables, I was getting errors "Must declare the scalar variable "@.TotalRows". which I think it is the result of the variables scope.

|||Sorry, I had left a [GO] in the script, and it starts a new variable scope.