0

What is the best way to get the names of all of the tables in all Databases on all Sql Servers? There are many databases in many Servers. So I wanna list Server names and database names with table names. For example

Id ServerName databaseName    Tablename
1  server1    Product         Vegetables
2  server2    Product         Milks
3  server1    Customer        People

Probably I will use following query with openquery() but I did'nt do it. 

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
INSERT INTO @AllTables (CompleteTableName)
    EXEC sp_msforeachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id'
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1
HAKAN MAZI
  • 3
  • 6
  • Does this answer your question? [How do I get list of all tables in a database using TSQL?](https://stackoverflow.com/questions/175415/how-do-i-get-list-of-all-tables-in-a-database-using-tsql) – Morpheus Mar 13 '20 at 17:35

2 Answers2

0
delete from [DatabaseName].[dbo].[hak_tables]
declare @counter int;
declare @openquery nvarchar(2000),  @tsql nvarchar(2000), @servername nvarchar(2000)

create table #temp(
    id int,
    servername nvarchar(50)
)

insert into #temp(id,servername)
values (1,'ServerName'),
       (2,'[ServerName -2]'),
       (3,'[ServerName -3]')

create table #tablehavuzu(
       ID int identity (1,1),
       sunucuAdi nvarchar(1000),
       databaseName nvarchar(1000),
       name nvarchar(1000),
        type nvarchar(1000),
        create_date datetime,
       modify_date datetime
)

declare @cmd3 nvarchar(max)
set @counter = 1
while @counter <= (select count(1) from #temp)
begin
       create table #databasename(
             name nvarchar(1000),
             counter int
       )
       set @servername = (select servername from #temp where id=@counter)
       declare @sorg nvarchar(max)
       set @sorg='insert into #databasename
       select name,ROW_NUMBER() over(order by name asc) from '+@servername+'.master.sys.databases '
       exec (@sorg)

       declare @counter1 int;
       set @counter1 = 1
       declare @databaseCount int
       set @databaseCount = (select count(1) from #databasename)
       while @counter1 <= @databaseCount
       begin
       declare   @databaseName nvarchar(1000),@sql nvarchar(1000)
             begin try
             set @databaseName = (select name from #databasename where counter=@counter1)
             set @sql ='select @@SERVERNAME sunucuAdi, '''''+@databaseName+''''' as databaseName, name, type, create_date, modify_date from '+@servername+'.'+@databaseName+'.sys.tables'

             set @cmd3= 'select * from openquery('+@servername+','''+@sql+''')'
             insert into #tablehavuzu
             exec (@cmd3)
             end try

             begin catch
             end catch
                    set @counter1 = @counter1 +1;
       end
       set @counter = @counter + 1;
       drop table #databasename
end
insert into [M3].[dbo].[hak_tables]
select *  from #tablehavuzu

drop table #temp
drop table #tablehavuzu
HAKAN MAZI
  • 3
  • 6
0

The below query will list all the table of all the databases in an instance - So you can execute it in all the instances to get those - 

declare @sql nvarchar(max);

select @sql = 
    (select ' UNION ALL
        SELECT ' +  + quotename(name,'''') + ' as database_name,
               s.name COLLATE DATABASE_DEFAULT
                    AS schema_name,
               t.name COLLATE DATABASE_DEFAULT as table_name 
               FROM '+ quotename(name) + '.sys.tables t
               JOIN '+ quotename(name) + '.sys.schemas s
                    on s.schema_id = t.schema_id'
    from sys.databases 
    where state=0
    order by [name] for xml path(''), type).value('.', 'nvarchar(max)');

set @sql = stuff(@sql, 1, 12, '') + ' order by database_name, 
                                               schema_name,
                                               table_name';

execute (@sql);
Prabir
  • 56
  • 7