Just curious - In what scenario does it happen ?

stylish · Sep 18, 2018 9:32 AM

USE master GO CREATE PROCEDURE dbo.sp_FindStringInTable @stringToFind VARCHAR(100), @schema sysname, @table sysname AS BEGIN TRY DECLARE @sqlCommand varchar(max) = 'SELECT * FROM [' + @schema + '].[' + @table + '] WHERE ' SELECT @sqlCommand = @sqlCommand + '[' + COLUMN_NAME + '] LIKE '' + @stringToFind + '' OR ' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') SET @sqlCommand = left(@sqlCommand,len(@sqlCommand)-3) EXEC (@sqlCommand) PRINT @sqlCommand END TRY BEGIN CATCH PRINT 'There was an error. Check to make sure object exists.' PRINT error_message() END CATCH

Dev_ · Sep 18, 2018 9:36 AM

Thanks Dev_ I ran your skript and got 'Command(s) completed successfully.'Now how do I get my answer ?

phone · Sep 18, 2018 11:27 AM

The skript provided by 'Dev_', creates SP and then you have to execute SP with input value sto get results.. try below skript- ( 1 quick search gave me this....), just run in each database or you can add code to run in all DB at once... But depending upon the size of DB and data in table, this will take some time @stylish - I have used once in the past...We had a process that we used to synch data from multiple tables between two or more sources. tables were divide into groups. One group kept failing complaining about data type and just gave the value out. Since this was in early stage of process, we didn't have enough error handling to point out which tables and or columns. We knew list of tables from group. Or it can also used in cases where one or some value in ur DB needs to be updated by new value (because old value was no longer in use...I know different way to handle it with FK relation but if that was not option then..) . You can either search each tables or columns one at a time, or do search on all tables and columns at once. Create Update skripts at once based on your results output and do mass replacements. ---------------------------------------------------------------------------------------- DECLARE @SearchStr nvarchar(100) SET @SearchStr = '## YOUR STRING HERE ##' -- 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 -- Updated and tested by Tim Gaunt -- http://www.thesitedoctor.co.uk -- http://blogs.thesitedoctor.co.... -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010 -- Date modified: 03rd March 2011 19:00 GMT CREATE TABLE #Results (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) + '.' + QUOTENAME(TABLE_NAME)) 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', 'int', 'decimal') 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 DROP TABLE #Results link here - http://thesitedoctor.co.uk/blo... Last edited: 18-Sep-18 02:43 PM

STUPIDA · Sep 18, 2018 2:41 PM

Thank All

phone · Sep 19, 2018 11:28 AM