Simple Dynamic SQL

Here are a couple of examples to follow.

USE master;

Exec sp_MSforeachdb

' Declare @SQL varchar(max)    
 Set @SQL = ""    
 Select @SQL = @SQL +
 Coalesce(
 "Select Distinct
 *
  from "
    + name 
    + ".information_schema.tables     
    where table_name like ""addressr%""  UNION ","" )    
 from sys.databases where state_desc = "ONLINE"    
 set @SQL = 
 Substring(@SQL, 1, Len(@SQL) - 6)
 + " order by table_catalog  "     exec (@SQL)
 '

USE Master
GO

DECLARE

    @Server nvarchar(255) = 'serverone',
    @Database nvarchar(255) = 'northwind',
    @Schema nvarchar(255) = 'cust%',
    @Table nvarchar(255) = '%address%',
    @Col nVarchar(255) = '%',
    @sql nvarchar(4000)
      
    if @Schema is null
        set @Schema = 'dbo'

    set @sql =    'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION FROM '
    + space(1) + @Server + '.' + @Database + '.INFORMATION_SCHEMA.COLUMNS'
    + space(1) +    'WHERE TABLE_CATALOG like ''' + @Database + ''''
    + space(1) +    'AND TABLE_SCHEMA like ''' + @Schema + ''''
    + space(1) +    'AND TABLE_NAME like ''' + @Table + ''''
    + space(1) +    'AND COLUMN_NAME like ''' + @Col + ''''
    + space(1) +    'ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME ASC'

    exec sp_executesql @sql output


DECLARE
@A nVarchar(100),
@Cont INT = 1,
@Cnt INT = (SELECT COUNT (1) FROM sys.objects where name like '%address%' and type_desc = 'user_table'),
@SQL nVarchar(4000)
DECLARE @Tbl TABLE (ID INT Identity, Name nVarchar(100));
--need to add dymamic schema identification here
--need to also query both views and tables
INSERT @Tbl
(Name)

SELECT
name FROM sys.objects where name like '%address%' and type_desc = 'user_table'
WHILE
@Cont <= @Cnt
BEGIN
SET
@A = (SELECT NAME FROM @Tbl WHERE ID = @Cont)
SELECT
@A AS 'Table'
SELECT
@SQL = 'SELECT COUNT (*) FROM '
SELECT
@SQL = @SQL + @A
EXEC
(@SQL)
SET
@Cont = @Cont + 1
END