May 11, 2020 at 3:48 am
Hi Forum,
I need to find a Value in a database and have seen some useful code to do this.
The only thig is there's littl explanation on how to use it or how it works.
Note this helpful link
https://www.sqlservercentral.com/forums/topic/how-to-find-a-value-in-all-column-of-all-table-in-a-db
My needs are slightly different, I just want to search for a value, whether it be Numeric or Text and get a return the Table & Field it belongs to, and if there's more than one match, collect that as well.
Can anyone help?
Thanks
May 11, 2020 at 1:48 pm
The only way to do this is some sort of looping mechanism that checks each table/field that matches the datatype and then return results or store them in a table. There are numerous scripts in the script library that do something similar. Likely you need to modify them to capture the field when it's found.
https://www.sqlservercentral.com/search/search+all+tables+for+a+value/section/scripts
May 14, 2020 at 6:20 am
Thanks Steve, I went to that link & eventually got to the link below which has exactly what I wanted. Cheers Peter
--https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'X' -- CHANGE SEARCH VALUE HERE
DECLARE tables_cur CURSOR FOR SELECT ss.name +'.'+ so.name [name], object_id FROM sys.objects so INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id
AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + ']
LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
EXECUTE(@sql_string)
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
May 14, 2020 at 2:23 pm
Glad that worked. Good luck
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply