August 18, 2010 at 6:20 am
Hello,
I have table Employee,in the table one column having ''abc" value.
I need to find what all the tables using the "abc" value.
How to find through Query.
could you anyone help on this.
Thanks&Regards
ARP
August 18, 2010 at 11:12 am
Are you looking in multiple tables for "abc"?
Or are you looking at multiple rows in the ONE table where the column has a value of "abc"?
Posting scripts to recreate the schema, add sample data, and then show expected output would be very helpful.
August 18, 2010 at 12:03 pm
It sounds like you want to search all columns in all tables in the entire database for occurrences of this value? Is that correct?
August 18, 2010 at 9:35 pm
"It sounds like you want to search all columns in all tables in the entire database for occurrences of this value? Is that correct?"
Yes, am looking column value in all tables in the entire database.
Thanks
August 18, 2010 at 9:40 pm
Are you looking in multiple tables for "abc"?
Or are you looking at multiple rows in the ONE table where the column has a value of "abc"?
Posting scripts to recreate the schema, add sample data, and then show expected output would be very helpful.
Yes, am looking in multiple tables for "abc" in the entire database.
For example, We have Employee table and Employee name is "abc".
I need that employee name "abc" wherever we are using in the database from the tables.
Thanks
August 18, 2010 at 9:44 pm
There is no easy way to do this. A database isn't like the file system where you search everything. A search across different tables requires different queries with separate FROM clauses. Same for different columns.
If you want every occurrence, you can try this, but it will not perform well: http://www.sqlservercentral.com/scripts/Search/69345/
August 18, 2010 at 9:49 pm
For simple, I like Steve's idea.
If you need to search the entire database often for strings that may or may not be an employee name then you may want to look at Full Text Search functionality.
Of course, that comes with considerable overhead so do see if the first suggestion will do, it may save a bunch of headaches.
November 30, 2017 at 9:11 pm
You can find values using this selection tool: Search values anywhere in selected SQL Server database.
In depth explanation how the script works
It uses different script depending on what type of value thas i searched for. Number, dates or string
Here is a sample
IF OBJECT_ID('tempdb..#TFindTextValueInDatabase') IS NOT NULL DROP TABLE #TFindTextValueInDatabase
CREATE TABLE #TFindTextValueInDatabase (object_id INT, FName NVARCHAR(256), FTableName NVARCHAR(256), FColumnName NVARCHAR(256), FValue NVARCHAR(3630))
DECLARE @sTableName nvarchar(256), @sColumnName nvarchar(128), @sCondition nvarchar(2000), @object INT
DECLARE @sFindText nvarchar(2000)
SET @sTableName = ''
-- !!!!!!!!!!!! Replace USA to the value you are searching for
SET @sFindText = '"__" = ''USA'''
WHILE @sTableName IS NOT NULL
BEGIN
SET @sColumnName = ''
SELECT @sTableName = MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)),
@object = OBJECT_ID( MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @sTableName
AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0
WHILE (@sTableName IS NOT NULL) AND (@sColumnName IS NOT NULL)
BEGIN
SET @sColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@sTableName, 2)
AND TABLE_NAME = PARSENAME(@sTableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @sColumnName
)
IF @sColumnName IS NOT NULL
BEGIN
SET @sCondition = REPLACE(@sFindText , '"__"', @sColumnName ) -- Replace '__' with active column name
-- Insert records from result into TFindTextValueInDatabase
INSERT INTO #TFindTextValueInDatabase
EXEC (
'SELECT ' + @object + ', ''' + @sTableName + '.' + @sColumnName + ''', + PARSENAME( ''' + @sTableName + ''', 1), PARSENAME( ''' + @sColumnName + ''', 1 ), LEFT(' + @sColumnName + ', 3630)
FROM ' + @sTableName + ' (NOLOCK) ' +
' WHERE ' + @sCondition
)
END
END
END
SELECT * FROM #TFindTextValueInDatabase
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply