You could do something like this using dynamic sql:

USE DATABASE_NAME -- your database name

--variables
DECLARE
    @SQL NVARCHAR(MAX),
    @CRLF NVARCHAR(2) = CHAR(13) + CHAR(10)

--generate your sql
SELECT
    @SQL = COALESCE(@SQL, @CRLF)
        + 'SELECT '''
            + TABLE_NAME + ''' AS TABLE_NAME '
        + 'FROM '
            + TABLE_SCHEMA + '.' + TABLE_NAME
        + 'WHERE '
            + 'CarID = 1 ' + @CRLF
        + 'UNION '
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME = N'CarID'

--remove the last union
SET @SQL = LEFT(@SQL, LEN(@SQL) - 6)

--preview the generated sql
PRINT @SQL

--execute
--EXEC (@SQL)

Here we are using the INFORMATION_SCHEMA.COLUMNS to identify all tables containing the particular column you want.

Using that, we then generate a sql query to return only the table names where the CarID = 1 is found, ignoring all other tables, or even the tables where you have this column, but not the id 1.

You can preview the query (on PRINT @SQL) and execute it separately, or you can uncomment EXEC (@SQL) to run all together.

***Even you have cardid=1 for multiple rows table name will be selected once.

Sample Tables

-- Create first table
CREATE TABLE Table1 (
    Id INT PRIMARY KEY,
    Name VARCHAR(50),
    CardId INT
);

INSERT INTO Table1 (Id, Name, CardId)
VALUES
    (1, 'John', 1),
    (2, 'Jane', 2),
    (3, 'Bob', 1);

-- Create second table
CREATE TABLE Table2 (
    Id INT PRIMARY KEY,
    Address VARCHAR(100),
    CardId INT
);

INSERT INTO Table2 (Id, Address, CardId)
VALUES
    (1, '123 Main St.', 2),
    (2, '456 Elm St.', 1),
    (3, '789 Oak St.', 3);

-- Create third table
CREATE TABLE Table3 (
    Id INT PRIMARY KEY,
    Email VARCHAR(50),
    Phone VARCHAR(20),
    CardId INT
);

INSERT INTO Table3 (Id, Email, Phone, CardId)
VALUES
    (1, 'john@example.com', '555-1234', 1),
    (2, 'jane@example.com', '555-5678', 2),
    (3, 'bob@example.com', '555-9012', 1);

Query:

enter code here

DECLARE @SearchColumn NVARCHAR(255) = 'CardId';
    DECLARE @SearchValue INT = 1;
    DECLARE @SQL NVARCHAR(MAX);
    
    SET @SQL = (
         SELECT top 1 string_agg('SELECT top 1''' + TABLE_NAME + ''' AS TableName 
            FROM ' + TABLE_NAME + '
            WHERE ' + @SearchColumn + ' = ' + CAST(@SearchValue AS NVARCHAR(255)) + ' ',' union all ')
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = @SearchColumn        
        
    );
    
    EXEC sp_executesql @sql

Output:

fiddle

Only List TableNAmes

DECLARE  @ColumnName nvarchar(100) ='CarId'
DECLARE @ID int=2
create table #listTable (tableName varchar(100))
declare @sql nvarchar(max)= 'insert into #listTable SELECT  ''?'' FROM ? WHERE '+@ColumnName +'='''+cast(@ID as varchar(100))+''''
declare @whereand nvarchar(max)='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME='''+@ColumnName+''')'
EXEC sp_MSforeachtable @command1=@sql,@whereand=@whereand

select * from #listTable

all records

--USE [DataBase]
DECLARE  @ColumnName nvarchar(100) ='CarId'
DECLARE @ID int=2

declare @sql nvarchar(max)= ' SELECT * FROM ? WHERE '+@ColumnName +'='''+cast(@ID as varchar(100))+''''
declare @whereand nvarchar(max)='AND O.ID IN (SELECT OBJECT_ID FROM SYS.COLUMNS C WHERE C.NAME='''+@ColumnName+''')'
EXEC sp_MSforeachtable @command1=@sql,@whereand=@whereand

I have a bespoke procedure which I wrote which could do this for you.

It's designed to search for text that can be in any column in any table in any database, one of its options allows you to pass a list of terms to match with (or exclude) column names.

It's a system procedure, you create it in the master database and register it as a system proc, you can then use it within any database context.

For your specific case you would use it like so:

exec sp_FindTextInAnyTable @Find = '1', @CnameList = 'CarId', @SearchNumbers = 1;

It returns a list of tables where the searched for argument is found, the column(s) containing it and some SQL that you can cut and paste to find rows with the searched for data.

The full code is below. After creating in master you would then use exec sys.sp_MS_marksystemobject dbo.sp_FindTextInAnyTable;

There are many customisations possible to limit the searching, some bespoke to the environemnt we use it in such as for optional including temporal table with the assumed history schema etc, parameters are documented in the code below.

Please do try out and let me know if it works for you?

Create procedure sp_FindTextInAnyTable
@Find nvarchar(100), /* Like predicate - supply wildcards in the value */
@SearchNumbers tinyint = 0, /* If searching for numbers: 0 = look at text type columns only 1 = look at numeric type columns only, 2 = look at all types */
@Schema sysname = null, /* search only tables in a specific schema or NULL for all schemas */
@IncludeMax bit = 0, /* Set to 1 to include varchar(max) columns, by default these are excluded */
@IncludeHistory bit = 0, /* Set to 1 to include history tables, by default these are excluded */
@IncludeAudit bit = 0, /* Set to 1 to include CreatedBy / AlteredBy columns, by default these are excluded */
@MaxTables int = -1, /* Set the maximum number of tables to search, set to -1 for no limit (be careful and only run on a replica in production) */
@TnameList varchar(200) = null, /* Comma-delimited list of words to match with table names to search or NULL to search all tables; prefix the list with ~ to exclude tables instead */
@CnameList varchar(200) = null, /* Comma-delimited list of words to match with column names to search or NULL to search all columns; prefix the list with ~ to exclude columns instead (except audit columns) */
@RowLimit bigint = 100000, /* Default max size of table in rows - prevents hitting large tables unless required, ignored if specific list of tables used */
@Refresh bit = 0, /* Set to 1 to have existing search re-evaluated, otherwise data is returned from last search result */
@Debug bit = 0
as
set nocount, xact_abort on;

/*

Run this in the context of any database to search within every CHAR or VARCHAR column and optionally NUMERIC columns in every table in the database for the specified text to find.

If the text exists in any row it returns the column and table containing the text and
the specific sql to run to find the text in the table.

example usage
exec sp_FindTextInAnyTable @Find = 'black'; /* Find any table with specific value "black" in any column, excludes any history tables, audit columns and varchar(max) columns, limits to first 50 tables */
exec sp_FindTextInAnyTable @Find = '%spoke%'; /* Find the text "spoke" within text any column in any table  */
exec sp_FindTextInAnyTable @Find = '%sys%', @IncludeAudit = 1; /* Find within any column in any table, include searching within createdby/alteredby columns  */
exec sp_FindTextInAnyTable @Find = '%sys%', @Refresh = 1; /* Repeat a previous search and check all tables again instead of returning cached results */
exec sp_FindTextInAnyTable @Find = '%scot%', @TnameList = 'file, log', @Refresh = 1; /*Find any column containing the text in only in tables with "file" or "log" in its name */
exec sp_FindTextInAnyTable @Find = '%scot%', @TnameList = 'file, log', @IncludeHistory = 1, @Refresh = 1; /* Repeat the above search but also search in History tables */
exec sp_FindTextInAnyTable @Find = '%scot%', @Refresh = 1, @Schema = 'history' /* Only search the History tables */
exec sp_FindTextInAnyTable @Find = '%scot%', @TnameList = '~file, log',@Refresh = 1; /* Search all tables except those with file or log in the name */

*/
declare @sql nvarchar(max), @br varchar(2) = Iif(@Debug = 1, Char(13), ''), @Valid bit = 0, 
    @TExclude bit = (Iif(@TnameList like '~%', 1, 0)), @CExclude bit = (Iif(@CnameList like '~%', 1, 0));
declare @Union varchar(20) = Concat(' union all ', @br);
declare @TNames table(tname sysname);
declare @CNames table(cname sysname);

insert into @TNames(tname) 
select Trim([value]) 
from String_Split(Replace(@TnameList, '~',''), ',');

insert into @CNames(cname) 
select Trim([value]) 
from String_Split(Replace(@CnameList, '~',''), ',');

if Object_Id('master.dbo.sp_FindTextInAnyTableResults') is null
begin;
    create table master.dbo.sp_FindTextInAnyTableResults(
        DBId int not null, 
        Find nvarchar(100) not null, 
        Tname sysname not null, 
        Cname sysname not null, 
        Row_Count bigint not null, 
        Datatype varchar(50), [SQL] varchar(max) not null, 
        CreateDate datetime2(0) not null default(GetDate()), 
        Id int identity
    );
    alter table dbo.sp_FindTextInAnyTableResults add constraint [PK_sp_FindTextInAnyTableResults] primary key clustered (DBId, Find, Id) with (fillfactor = 100) on [PRIMARY];
end;

if @Refresh = 1 or @Debug = 1 or not exists (select * from master.dbo.sp_FindTextInAnyTableResults where DBId = Db_Id() and Find = @Find)
begin
    delete from master.dbo.sp_FindTextInAnyTableResults where DBId = Db_Id() and Find = @Find;

    with TList as (
        select Concat_Ws('.', QuoteName(Schema_Name(t.schema_id)), 
            QuoteName(t.[name])) TName, 
            c.[name] CName,
            dt.[name] DTName,
            dt.system_type_id,
            c.max_length,
            c.precision, c.scale,
            Dense_Rank() over(order by t.[name]) Tcount,
            p.row_count
        from sys.columns c
        join sys.dm_db_partition_stats p on p.object_id = c.object_id and p.index_id < 2 and p.row_count > 0
        join sys.tables t on t.object_id = c.object_id and (@Schema is null or t.schema_id = Schema_Id(@Schema)) and Schema_Name(t.schema_id) != 'datasync'
        join sys.types dt on dt.user_type_id = c.user_type_id 
        where (
            (dt.system_type_id in (167,175,231,239) and (@SearchNumbers in (0,2) or (Try_Convert(int, @Find) is null and Try_Convert(decimal, @Find) is null and Try_Convert(money, @Find) is null)))
            or (@SearchNumbers > 0 and Try_Convert(int, @Find) is not null and (dt.[name] like '%int%') )
            or (@SearchNumbers > 0 and Try_Convert(decimal, @Find) is not null and (dt.[name] like '%decimal%' or dt.[name] like '%numeric%' or dt.[name] like '%real%'  or dt.[name] like '%float%') )
            or (@SearchNumbers > 0 and Try_Convert(money, @Find) is not null and (dt.[name] like '%money%') )
        )
        and (@IncludeHistory = 1 or Schema_Name(t.schema_id) != 'History' or @Schema = 'History')
        and (c.max_length >= Len(@Find) or (c.max_length = -1 and @IncludeMax = 1) or (dt.[name] not like '%char%'))
        and (@IncludeAudit = 1 or not (c.[name] = 'createdby' or c.[name] = 'alteredby'))
        and (@TnameList is null or (
                (@TExclude = 0 and exists (select * from @TNames where CharIndex(tname, t.[name]) > 0)) or 
                (@TExclude = 1 and not exists (select * from @TNames where CharIndex(tname, t.[name]) > 0))
            )
        )
        and (@CnameList is null or (
                (@CExclude = 0 and exists (select * from @CNames where CharIndex(cname, c.[name]) > 0)) or 
                (@CExclude = 1 and not exists (select * from @CNames where CharIndex(cname, c.[name]) > 0))
            )
        )
        and (@RowLimit = -1 or (@TnameList is not null and @TExclude = 0) or p.row_count <= @RowLimit)
        and c.is_computed = 0 and c.is_hidden = 0
    )
    /*select * from tlist order by 1,2 end;*/

    select @sql = 
        Concat(
            'insert into master.dbo.sp_FindTextInAnyTableResults(DBId, Find, Tname, Cname, Row_Count, DataType, [SQL])', 
            @br, 
            String_Agg([sql], @Union)
        ), 
        @Valid = IsNull(Max(Iif([sql] is not null, 1, 0)), 0)
    from (
        select Convert(varchar(max), 
            Concat(
                'select top(1) db_id(), '
                , '''', @Find, ''''
                , ', ''', TName, ''''
                , ', ''', CName, ''''
                , ', ', row_count
                , ', DataType = ', 
                    Concat(
                            ''''
                        , DTName
                        , case when system_type_id in (167,175,231,239) then Iif(max_length = -1, '(max)', Concat('(', max_length, ')')) end
                        , case when DTName like '%decimal%' or DTName like '%numeric%' or DTName like '%real%' or DTName like '%float%' then '(' + Concat_Ws(',', precision, scale) + ')' end
                        , ''''
                    )
                , ', SqlToRun = ',
                    Concat(''''
                        , 'Select * from '
                        , QuoteName(Db_Name()), '.', TName
                        , ' where '
                        , case when system_type_id in (167,175,231,239) then QuoteName(CName) else Concat('try_convert(varchar(50), ', QuoteName(CName), ')') end
                        , ' like ', ''''''
                        , case when system_type_id in (167,175,231,239) then @Find else Concat('%', @Find, '%') end
                        , '''''', ''''
                    )
                , ' from ', TName
                , ' where ', case when system_type_id in (167,175,231,239) then QuoteName(CName) else Concat('try_convert(varchar(50), ', QuoteName(CName), ')') end
                , ' like '''
                , case when system_type_id in (167,175,231,239) then @Find else Concat('%', @Find, '%') end,
            '''')
        )[sql]
        from TList
        where @MaxTables = -1 
            or Tcount <= @MaxTables 
            or @TnameList is not null
    )[sql];
    
    if @Debug = 1
        begin
        select @sql;
        print @sql;
        end
    else
        if @Valid = 1 exec (@sql);
end

select Concat(
    'Found in table '
        , Tname
        , ', Rows = ', Max(Row_Count)
        , ', Column', Iif(Count(*) > 1, 's', ''), ': '
        , String_Agg(Concat_Ws(' ', Cname, Datatype), ', ')
    ) FoundIn,
    String_Agg([SQL], ';' + Char(13)) + ';' [SQL]
from master.dbo.sp_FindTextInAnyTableResults
where DBId = Db_Id() and Find = @Find
group by Tname
order by Tname;

GO

select
  'select ' + quotename(t.[name],'''')+' as [table],* from ' + quoetname(t.name) + ' where CarID=1'
from sys.tables t
inner join sys.columns c on c.object_id=t.object_id
where c.name='CarID'

run it, copy-paste the results into a new query window, run em