Select only databases with a certain table name and specific columns So my first query is this one:

SELECT * 
FROM information_schema.columns 
WHERE table_name='logsys' and 
column_name in
('CD_LOG'
,'NM_USUARIO'
,'DT_ACAO'
,'DS_HISTORICO'
,'NOME_MAQUINA'
,'USUARIO_MAQUINA'
,'DATA_REFERENCIA'
,'ID_SISTEMA'
,'VERSAO'
,'LOCAL'
,'ACAO'
,'ID_LOG'
)


Then to run it in all databases, I made this:

DECLARE @command varchar(1000) 
SELECT @command = 

   'use [?] SELECT 
   DB_NAME() as DatabaseName, * 
FROM information_schema.columns 
WHERE table_name=''logsys'' and  column_name in
(''CD_LOG'',''NM_USUARIO'',''DT_ACAO'',''DS_HISTORICO'',''NOME_MAQUINA'',''USUARIO_MAQUINA'',''DATA_REFERENCIA''
,''ID_SISTEMA'',''VERSAO'',''LOCAL'',''ACAO'',''ID_LOG'')'

EXEC sp_MSforeachdb @command 


But I'm just confused about something. I want THESE specific columns. I want tables with all these columns. not more, not less. but with in, obviously it lists tabes that have at least one of these columns. I'm confused about this simple query. How can I use it, in all databases, to find only those tables that has all those columns?

Should I use and column = x, and column= y.... and so on?

And a bonus question. How can I insert foreachdb inside a table?

EDIT1:

Thanks for answers, for now, I have this:

--CRIA TABELA DO INSERT 
CREATE TABLE TB_DB_LOGSYS
    (
        DATABASENAME VARCHAR(200),
        TABLE_CATALOG VARCHAR(200),
        TABLE_NAME VARCHAR(200),
        COLUMN_NAME VARCHAR(200),
        DATA_TYPE VARCHAR(100)
    )
GO
---------------------------------------------------------------------------
--PROCURA AS LOGSYS QUE POSSUEM 12 COLUNAS 
TRUNCATE TABLE TB_DB_LOGSYS
DECLARE @command varchar(1000) 
SELECT @command = 

   'use [?] SELECT 
   DB_NAME() as DatabaseName,  TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.columns 
WHERE table_name=''logsys'' and  column_name in
(''CD_LOG'',''NM_USUARIO'',''DT_ACAO'',''DS_HISTORICO'',''NOME_MAQUINA'',''USUARIO_MAQUINA'',''DATA_REFERENCIA''
,''ID_SISTEMA'',''VERSAO'',''LOCAL'',''ACAO'',''ID_LOG'')'

--INSERE O FOREACHDB NA TABELA
INSERT INTO TB_DB_LOGSYS
EXEC sp_MSforeachdb @command 
---------------------------------------------------------------------------
SELECT * FROM TB_DB_LOGSYS

SELECT DATABASENAME, COUNT(COLUMN_NAME) AS [COUNT] FROM TB_DB_LOGSYS
GROUP BY DATABASENAME
HAVING COUNT(COLUMN_NAME)=12


but using having is not what I really want. maybe there's another column, and not the one I'm looking for.
I've set up this sample on fiddle.uk just to show you one option.


CREATE TABLE logsys_a(id int, v1 int, v2 int, v3 int, v4 int, v5 int);
CREATE TABLE logsys_b(id int, v1 int, v2 int,         v4 int, v5 int);
CREATE TABLE logsys_c(id int, v1 int, v2 int, v3 int, v4 int, v5 int);
GO



Instead of use USE [?] you can use TABLE_CATALOG to filter on which database are you searching for. In my example I've used LIKE 'fiddle%' because it changes every time you executes the query.
You can check if EXISTS a table with all the required columns using HAVING COUNT() = x, as you can see in this case it only returns logsys_a and logsys_b.

As far as I don't know if you need all table columns I haven't used again COLUMN_NAME IN ('v1','v2','v3','v4','v5') on the main query.


SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM   INFORMATION_SCHEMA.COLUMNS T1 
WHERE  TABLE_CATALOG LIKE 'fiddle%'
AND    TABLE_NAME LIKE '%logsys%'
AND    EXISTS (SELECT   T2.TABLE_SCHEMA, T1.TABLE_NAME
               FROM     INFORMATION_SCHEMA.COLUMNS T2
               WHERE    TABLE_CATALOG LIKE 'fiddle%'
               AND      T2.TABLE_SCHEMA = T1.TABLE_SCHEMA
               AND      T2.TABLE_NAME = T1.TABLE_NAME
               AND      T2.COLUMN_NAME IN ('v1','v2','v3','v4','v5')
               GROUP BY T2.TABLE_SCHEMA, T2.TABLE_NAME
               HAVING COUNT(*) = 5);

GO


TABLE_CATALOG                           | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME
:-------------------------------------- | :----------- | :--------- | :----------
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | id         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v1         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v2         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v3         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v4         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_a   | v5         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | id         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v1         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v2         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v3         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v4         
fiddle_a763285347ec46bb9b7fe95d5a6e754b | dbo          | logsys_c   | v5         


dbfiddle here