Examples

IF OBJECT_ID('tempdb..#tempTableName') IS NOT NULL
	DROP TABLE #tempTableName;

IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='IDX_cdp_Account' 
    AND object_id = OBJECT_ID('[cdp].[Account]'))
  begin
    DROP INDEX [IDX_cdp_Account] ON [cdp].[Account]
  end

USE DB
GO

SELECT
T.name as TableName
,c.name as ColumnName
,s.name as ColumnType
FROM sys.tables T
JOIN sys.columns c
ON c.object_id = t.object_id
JOIN sys.types s
ON s.system_type_id = c.system_type_id
WHERE T.name IN
(
 'tableName'
)
order by 1

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS

USE MyDB
GO

DECLARE
@OldItem nvarchar(50) = 'OldValue',
@NewItem nvarchar(50) = 'NewValue'


DECLARE @tables table (ColumnName nvarchar(100), TableName nvarchar(100))
INSERT INTO @tables (ColumnName, TableName)
SELECT
c.name  AS 'ColumnName',
t.name AS 'TableName'
FROM sys.columns c
JOIN  sys.tables t
ON c.object_id = t.object_id
WHERE
c.name = 'columnName1' OR c.name = 'columnName2'
ORDER BY
TableName,
ColumnName

DECLARE
@SQL NVARCHAR(2000),
@table nvarchar(100),
@column nvarchar(100)

WHILE ( EXISTS ( select top 1 1 from @tables ) )
BEGIN
	SET @table = (select top 1 TableName from @tables)
	SET @column = (select top 1 ColumnName from @tables where TableName = @table)

	--SET @SQL = N'select count(*) as CNT,''' + @table +  ''' as [Table] from VT_DWH.dbo.' + @table + ' where ' + @column + ' = ''' + @OldItem + ''''
	SET @SQL = N'UPDATE MyDB.dbo.' + @table + ' SET ' + @column + ' = ''' + @NewItem + '''  where ' + @column + ' = ''' + @OldItem + ''''

	PRINT(@SQL)
	--EXEC(@SQL)

	delete from @tables where TableName = @table
END

IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T

SELECT T.TABLE_CATALOG, T.TABLE_SCHEMA, T.TABLE_NAME, T.COLUMN_NAME
INTO #T
FROM INFORMATION_SCHEMA.COLUMNS T
where T.DATA_TYPE = 'nvarchar'
and CHARACTER_MAXIMUM_LENGTH >= '50'

DECLARE
@bd nvarchar(50),
@sc nvarchar(50),
@tab nvarchar(50),
@col nvarchar(50),
@pattern nvarchar(50) = 'searchString',
@sql nvarchar(4000) = ''

WHILE (EXISTS(select * from #T))
BEGIN

select top 1
@bd = T.TABLE_CATALOG,
@sc = T.TABLE_SCHEMA,
@tab = T.TABLE_NAME,
@col = T.COLUMN_NAME
FROM #T T

SET @sql = 'if exists(select top 1 * from ' + @bd + '.' + @sc + '.' + @tab + ' where ' + @col + ' like ''%' + @pattern + '%'') select top 1 '''+ @tab +''' as tab, * from ' + @bd + '.' + @sc + '.' + @tab + ' where [' + @col + '] like ''%' + @pattern + '%'''
print @sql
EXEC(@sql)

delete
FROM #T
where 1=1
and TABLE_CATALOG = @bd 
and TABLE_SCHEMA = @sc
and TABLE_NAME = @tab
and COLUMN_NAME = @col
END

SELECT ROUTINE_NAME, ROUTINE_DEFINITION, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE 1=1
--AND ROUTINE_NAME = 'FCT_FN_NAME'
AND ROUTINE_DEFINITION LIKE '%from table%'
--AND ROUTINE_TYPE='FUNCTION' --PROCEDURE FUNCTION
ORDER BY ROUTINE_NAME