SQL Server how to find specific data across entire database

I have this script which finds all tables that contain data in column RGNCODE. I’m trying to amend this to find tables that contain a specific value for rgncode across all tables. So RGNCODE = 'UK'. Unsure where to add this in?

SELECT
   sys.columns.name AS ColumnName,
   tables.name AS TableName
FROM sys.columns
JOIN sys.tables ON sys.columns.object_id = tables.object_id
WHERE
   sys.columns.name = 'rgncode' and 
   tables.name in (
      SELECT [TableName] = so.name
      FROM sysobjects so, sysindexes si
      WHERE so.xtype = 'U' AND si.id = so.id
      GROUP BY so.name
      HAVING max(si.rows) > 0 
   )

Answer

A dynamic statement, using system catalog views, is an option here:

DECLARE @stm nvarchar(max) = N''

SELECT @stm = @stm +
    CASE WHEN @stm = N'' THEN '' ELSE N'UNION ALL ' END +
    N'SELECT ''' +
    QUOTENAME(sch.[name]) + '.' + QUOTENAME(tab.[name]) + ''' AS TableName FROM ' +
    QUOTENAME(sch.[name]) + '.' + QUOTENAME(tab.[name]) +
    N' WHERE RGNCODE = ''UK'' HAVING COUNT(*) > 0 '
FROM sys.columns col
JOIN sys.tables tab ON col.object_id = tab.object_id
JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
WHERE col.[name] = 'RGNCODE'

PRINT @stm
EXEC sp_executesql @stm