How do I list all tables with their respective databases?

Question

I would like to list all the tables in my database that ends with a specific name and also all my databases that match these tables. I tried to do it but nothing comes back.

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

Answer 1

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 traverse in a cursor;
• Mounts a query dynamics using the tables sys.tables and sys.schemas in syntax [NOME DA DATABASE].[NOME DO SCHEMA].[NOME DA TABELA];
• Insert into a variable table in the formed nome_database, nome_schema and nome_tabela.
• At the end of the query with group by shows the amount of tables that corresponds to the filter by database;

Answer 2

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.

Answer 3

I finally managed to solve this problem I had. I found a specific solution to what I was looking for.I decided to post it here, because I think it might help someone else who might need it someday. :)

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

Answer 4

A solution to show which database is the searched table:

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

That might help you too:

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

The following is a list of xtype

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

Answer 5

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'