How do I list all the tables with their respective banks?

5

I would like to list all the tables in my database that end with a specific name and also all my databases that match those tables. I tried to do but it does not return anything to me.

Query

SELECT da.name AS BANCOS,ta.name as TABELAS
FROM sys.schemas da join  sys.tables ta
on da.schema_id = ta.schema_id
where ta.name like '%attach'
group by da.name, ta.name
    
asked by anonymous 05.06.2017 / 14:51

5 answers

6

The following query :

DECLARE @tabelas TABLE(nome_database SYSNAME,
                       nome_schema   SYSNAME,
                       nome_tabela   SYSNAME);
DECLARE @database SYSNAME;

SET NOCOUNT ON;

DECLARE bases CURSOR LOCAL FAST_FORWARD FOR
  SELECT d.name
    FROM sys.databases d;
OPEN bases
FETCH NEXT FROM bases INTO @database
WHILE @@FETCH_STATUS = 0
BEGIN
  INSERT INTO @tabelas(nome_database,
                       nome_schema,
                       nome_tabela)
  EXEC('SELECT ''' + @database + ''',
               s.name,
               t.name
          FROM ' + @database + '.sys.tables t
               INNER JOIN ' + @database + '.sys.schemas s ON s.schema_id = t.schema_id
         WHERE t.name LIKE ''%cliente''');

  FETCH NEXT FROM bases INTO @database;
END;
CLOSE bases;
DEALLOCATE bases;

SELECT *
  FROM @tabelas;

-- Resumo
SELECT nome_database,
       count(1) AS quantidade
  FROM @tabelas t
 GROUP BY nome_database;
  • Uses the table sys.database to scroll through a cursor ;
  • Assemble a% dynamic% using the tables query and sys.tables in syntax sys.schemas ;
  • Inserts a variable table in the form of [NOME DA DATABASE].[NOME DO SCHEMA].[NOME DA TABELA] , nome_database and nome_schema .
  • At the end, the nome_tabela with query shows the number of tables corresponding to the filter by group by ;
05.06.2017 / 15:38
4

I believe you can use something like this:

declare @BaseName varchar(100);
Declare @SelectTables varchar(1000);
--Primeiro obtenho em um cursor a lista dos banco de dados existentes
declare C_bases cursor for SELECT name FROM sys.databases;

--Abre o curosr
open C_bases;
-- Posiciona o cursor na primeira linha
FETCH NEXT FROM C_bases INTO @BaseName

-- Enquanto ainda tem linhas no cursor
WHILE @@FETCH_STATUS = 0  
BEGIN  

    --Imprimo o nome do banco 
    print @BaseName

    -- Monto o comando SQL que obtém a lista de tabelas da base onde o cursor esta posicionado
    -- Ex.: Na query abaixo eu listo as tabelas do banco [master] 
    -- SELECT * FROM [master].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
    set @SelectTables = 'SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME  FROM [' + @BaseName+  '].INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''';
    -- Executa o comando SQL gerado
    execute (@SelectTables);


  -- Posiciona o cursor na próxima linha ( ou próxima base no caso)
  FETCH NEXT FROM C_bases INTO @BaseName 
END   

--Fecha o cursor
CLOSE C_bases;  
--Desaloca o curosr
DEALLOCATE C_bases; 

Abs.

    
05.06.2017 / 15:15
2

I finally managed to solve this problem that I had. I came up with a specific solution of what I was looking for. I decided to post here because I think this could help someone else who might need one day. :)

DECLARE @banco_nome nvarchar(MAX), @tabela_nome nvarchar(MAX)

DECLARE @banco_cursor CURSOR  
DECLARE @sqlstatement nvarchar(MAX)
DECLARE @count_sql nvarchar(MAX)
DECLARE @total int

DECLARE @RegistrosFotograficos TABLE
(
  DatabaseName nvarchar(max),
  TableName nvarchar(max),
  Total int
)

SET @banco_cursor = CURSOR FORWARD_ONLY FOR
    SELECT name FROM sys.databases

OPEN @banco_cursor
FETCH NEXT FROM @banco_cursor INTO @banco_nome
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sqlstatement = 'DECLARE tabela_cursor CURSOR FORWARD_ONLY FOR SELECT TABLE_NAME FROM ' + @banco_nome + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME LIKE ''%ATTACH'' ORDER BY TABLE_NAME'
    EXEC sp_executesql @sqlstatement

    OPEN tabela_cursor
    FETCH NEXT FROM tabela_cursor INTO @tabela_nome
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @count_sql = 'USE ' + @banco_nome + '; SELECT @total=COUNT(1) FROM ' + @tabela_nome;
        EXECUTE sp_executesql @count_sql, N'@total int OUTPUT', @total=@total OUTPUT

        INSERT INTO @RegistrosFotograficos (DatabaseName, TableName, Total) VALUES (@banco_nome, @tabela_nome, @total);

        FETCH NEXT FROM tabela_cursor INTO @tabela_nome
    END
    CLOSE tabela_cursor;
    DEALLOCATE tabela_cursor;

    FETCH NEXT FROM @banco_cursor INTO @banco_nome
END
CLOSE @banco_cursor;
DEALLOCATE @banco_cursor;

SELECT * FROM @RegistrosFotograficos
    
06.06.2017 / 19:38
-1

A solution to show which database is the searched table:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%attach'

This may also help you:

SELECT *
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U' and  sobjects.name like '%attach' 

Follow xtype list

AF: Aggregate function (CLR) 
C: CHECK constraint 
D: Default or DEFAULT constraint 
F: FOREIGN KEY constraint 
L: Log FN: Scalar function 
FS: Assembly (CLR) scalar-function 
FT: Assembly (CLR) table-valued function 
IF: In-lined table-function 
IT: Internal table 
P: Stored procedure 
PC: Assembly (CLR) stored-procedure 
PK: PRIMARY KEY constraint (type is K) 
RF: Replication filter stored procedure 
S: System table SN: Synonym SQ: Service queue 
TA: Assembly (CLR) DML trigger 
TF: Table function 
TR: SQL DML Trigger 
TT: Table type 
U: User table 
UQ: UNIQUE constraint (type is K) 
V: View 
X: Extended stored procedure
    
05.06.2017 / 14:58
-1

Try something like:

SELECT * FROM sys.databases as db
INNER JOIN INFORMATION_SCHEMA.TABLES as tb ON db.name=tb.TABLE_CATALOG AND TABLE_NAME like '%attach'
    
05.06.2017 / 15:07