Sql-server – Select only databases with a certain table name and specific columns

join;sql serversql-server-2008-r2

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.

Best Answer

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

Related Solutions

Postgresql – Select rows with repeat data within certain timeframe

SELECT *
FROM   task t1
WHERE  EXISTS (
   SELECT 1
   FROM   task t2
   WHERE  t2.reference = t1.reference  -- same reference
   AND    t2.task_id  <> t1.task_id    -- different task_id (exclude self-join)
   AND    t2.end_date BETWEEN t1.end_date - 30
                      AND     t1.end_date + 30 
   )

An EXISTS semi-join should be the fastest way to arrive at DISTINCT rows, because you don't get duplicates to begin with. Also, in the event of multiple matching rows (many matching tasks withing 30 days) evaluation can stop after the first match is found. I would expect this query to beat anything you have so far. Test with EXPLAIN ANALYZE.

In a WHERE clause or JOIN condition don't use an expression like:

abs(t1.end_date - t2.end_date) < 30

If you can avoid it. As the left hand expression is derived from values of two tables, the only way for the query planner is to form a limited CROSS JOIN (after applying other conditions) and compute a value for every possible combination. This is a well known anti-pattern for good performance!

As long as there are only few rows per reference, it won't matter much. But the cost grows with O(N²) with more rows per reference. I rewrote the condition to:

t2.end_date BETWEEN t1.end_date - 30 AND t1.end_date + 30

In my tests, the first form went from 5x slower to 500x slower quickly when I narrowed down the id-space for reference (-> more matching rows).

The second form can also more easily use indexes. If you want to select a small sample of the table, with conditions like:

reference > 450
date_end > now()::date

And an index like:

CREATE INDEX test_idx ON task (reference, date_end);
CREATE INDEX test_idx2 ON task (date_end);

The performance gap becomes even more overwhelming.

Sql-server – Displaying Parent Child Information, With Certain Parent Columns Only Shown Once

A general policy is to let the reporting layer handle things like only printing ParentIncome once. However, since you are delivering a spreadsheet that will be used by others in who knows what manner, then I suppose you are stuck.

Because of the knowledge required you will need to develop some extra information (MIN, MAX, first, last, etc.) that is not known by a single row. There are dodges different from ROW_NUMBER() OVER (PARTITION...), but there will still be an extra step.

See the following:

CREATE TABLE #parent
(ParentNum INT,
 ParentName VARCHAR(20),
 ParentIncome INT);

CREATE TABLE #child
(ChildNum INT,
 ChildParentNum INT,
 ChildName VARCHAR(20),
 ChildAllowance INT);

INSERT INTO #parent VALUES(10,'John',50000);
INSERT INTO #parent VALUES(20,'Jane',55000);
INSERT INTO #parent VALUES(30,'Jackie',90000);

INSERT INTO #child VALUES(1,10,'Johnny',5)
INSERT INTO #child VALUES(2,20,'Jackie',10)
INSERT INTO #child VALUES(3,20,'Billy',5)
INSERT INTO #child VALUES(4,20,'Sally',5)
INSERT INTO #child VALUES(5,30,'Monique',0)

-- Basic approach you may be using

See this example SQL Fiddle #1

SELECT pc.ParentName, pc.ParentNum,
    CASE WHEN pc.RowNum = 1 THEN CAST(pc.ParentIncome AS VARCHAR(10)) ELSE '' END as ParentIncome,
    pc.ChildName, pc.ChildNum, pc.ChildAllowance
FROM (SELECT p.ParentNum, p.ParentName, p.ParentIncome, 
         c.ChildNum, c.ChildParentNum, c.ChildName, c.ChildAllowance,
         ROW_NUMBER() OVER (PARTITION BY ParentNum ORDER BY ParentNum) AS RowNum
       FROM #parent p JOIN #child c ON p.ParentNum = c.ChildParentNum) AS pc
ORDER BY pc.ParentNum, pc.ChildNum 

    -- An alternative, but still using a subselect for one element

See this example: SQL Fiddle #2

SELECT  p.ParentName, p.ParentNum, 
        CASE WHEN c.ChildNum = mc.MinChild THEN CAST (ParentIncome AS VARCHAR(10)) ELSE '' END AS ParentIncome,
        c.ChildName, c.ChildNum, c.ChildAllowance
       FROM #parent p 
          JOIN #child as c 
             ON p.ParentNum = c.ChildParentNum
          -- This subselect gives the MIN (or First) ChildNum per Parent
          JOIN (SELECT ChildParentNum, MIN(ChildNum) AS MinChild
                  FROM #child
                  GROUP BY ChildParentNum) AS mc 
             ON mc.ChildParentNum = c.ChildParentNum 
ORDER BY p.ParentNum, c.ChildNum 

drop table #parent
drop table #child

Notice that I cast the ParentIncome as a VARCHAR(10) so that the datatype would be of the same type as the empty income of ''. I originally used a NULL instead of a blank, but that might give you EXCEL problems.

Is it worth doing things this way? It is up to you, but it primarily depends on what you like best. The ROW_NUMBER() is a more powerful operator than MIN() and gives you more options, but in this case it appears that a MIN() will work for you.

Best Answer

Related Solutions

Postgresql – Select rows with repeat data within certain timeframe

Sql-server – Displaying Parent Child Information, With Certain Parent Columns Only Shown Once

Related Question