Stored procedure to check if table exists

You can check if a table exists in multiple ways -- for instance, using metadata table or by attempting to access it.

In either case, you will want to use dynamic SQL.

Also, I strongly recommend returning values from a stored procedure using OUTPUT parameters, not RETURN. If you want RETURN you probably want a stored function.

I like Sami's approach of passing in the names of the components you are looking for. Here is a version that should work:

CREATE PROCEDURE dbo.CheckTableCountyExists (
    @DatabaseName SysName,
    @SchemaName SysName,
    @TableName SysName,
    @TableExists BIT = 0 OUTPUT
) AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = '
SELET @TableExists =
          (CASE WHEN EXISTS (SELECT 1
                             FROM @DatabaseName.INFORMATION_SCHEMA.TABLES t
                             WHERE t.TABLE_SCHEMA = @SchemaName AND
                                   t.TABLE_NAME = @TableName
                            )
                THEN 1 ELSE 0
           END)';

    SET @sql = REPLACE(@sql, '@DatabaseName', QUOTENAME(@DatabaseName));

    EXEC sp_executesql @sql,
                       N'@SchemaName sysname, @TableName sysname, @TableExists BIT',
                       @SchemaName=@SchemaName, @TableName=@TableName, @TableExists=@TableExists;
END;

Then you call it as:

DECLARE @isExists BIT = 0;

EXEC dbo.CheckTableExists N'BlackCorsetDatabase', 
                          N'dbo', 
                          N'County', 
                          @isExists OUTPUT;

SELECT @isExists;

The above will fail, though, if the database does not exist. You can fix this with a TRY/CATCH. A generic method is:

CREATE PROCEDURE dbo.CheckTableCountyExists (
    @DatabaseName SysName,
    @SchemaName SysName,
    @TableName SysName,
    @TableExists BIT = 0 OUTPUT
) AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);

    SET @sql = '
SELET @TableExists =
          (CASE WHEN EXISTS (SELECT 1
                             FROM @DatabaseName.INFORMATION_SCHEMA.TABLES t
                             WHERE t.TABLE_SCHEMA = @SchemaName AND
                                   t.TABLE_NAME = @TableName
                            )
                THEN 1 ELSE 0
           END)';

    SET @sql = REPLACE(@sql, '@DatabaseName', QUOTENAME(@DatabaseName));
    BEGIN TRY
        EXEC sp_executesql @sql,
                       N'@SchemaName sysname, @TableName sysname, @TableExists BIT',

@SchemaName=@SchemaName, 
@TableName=@TableName, 
@TableExists=@TableExists;
     END TRY
     BEGIN CATCH
         -- on any error, assume table does not exist
         SET @TableExists = 0
     END CATCH;
END;

Note: You could be more specific about the error. However, it is hard to imagine a situation where the code works but you get an error in the dynamic SQL and want to return "1".

Here is a db<>fiddle.