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.
