The Mich
asked on
SQL Searching for Field Value - Value not Found - but we know it's there
Trying to find the value in a field - SQL Server 2008. I know the value exists, yet my SearchAllTables stored procedure doesn't show any values.
exec searchalltables '%Michelle%'
go
CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com + http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
DECLARE @Results TABLE(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
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
exec searchalltables '%Michelle%'
go
CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com + http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
DECLARE @Results TABLE(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
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
Qlemo
Check your stored procedure?! You are not showing what it does, so we cannot provide any help.
As we see, no results.. scnr.
Without your procedures code and data model and sample data, this is not really a solvable problem.
So sure, that you spelled it correctly? Maybe some part is case-sensitive? Do you use NO LOCK table hints?
Without your procedures code and data model and sample data, this is not really a solvable problem.
So sure, that you spelled it correctly? Maybe some part is case-sensitive? Do you use NO LOCK table hints?
try : exec searchalltables 'Michelle'
the procedure is also adding in the percent wildcard characters.
will look into it a bit more, but try that while I dig deeper.
the procedure is also adding in the percent wildcard characters.
will look into it a bit more, but try that while I dig deeper.
Hmmmmmm...... It works for me.
with or without the additional percent signs.
if you know that value is in a particular table, double check the table name et al using
Are you running multiple schema ?
with or without the additional percent signs.
if you know that value is in a particular table, double check the table name et al using
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME, COLUMN_NAME, DATA_TYPE
from information_schema.columns
where table_name = 'your table name'Are you running multiple schema ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I don't know what table the value is stored in - that's what I'm trying to find.
I'm not sure about multiple schema as I don't know what that means.
Should I remove the '(NOLOCK)'?
The user created custom fields in a program and added values. I'm pulling from the back end using SQL. I can see the values in the program but my search doesn't find the value on the SQL server. The search I referenced works - as it finds the user table w/my name in it, but we added that value (my name) to the custom fields and they don't appear.
I'm not sure about multiple schema as I don't know what that means.
Should I remove the '(NOLOCK)'?
The user created custom fields in a program and added values. I'm pulling from the back end using SQL. I can see the values in the program but my search doesn't find the value on the SQL server. The search I referenced works - as it finds the user table w/my name in it, but we added that value (my name) to the custom fields and they don't appear.
Need to find that table - are you sure you have the right database ?
Isnt there a connection string or setup / ini file you could check ?
You could try adding NTEXT and TEXT to the type of columns
The other thing to search for is any unrecognised tables with XML datatypes.
Isnt there a connection string or setup / ini file you could check ?
You could try adding NTEXT and TEXT to the type of columns
The other thing to search for is any unrecognised tables with XML datatypes.
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME, COLUMN_NAME, DATA_TYPE
from information_schema.columns
where data_type = 'XML'