List all table names without knowing the database name within my machine

Question

I am using SQl Server 2014, and Normally we can list out tables if we knew the DB name as :

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO

But I want to know all the tables irrespective of db's that are present on my machine

Or can I go for looping by listing out all DB name.(EXEC sp_databases)

Is there any better way to find out this?

@jarlh No man.. It just shows master DB tables . What I get with select count(*) from information_schema.tables = 6 where as select * from <anyDB>.information_schema.tables = 87. So this is not the Answer to list all the tables within my machine — Shivkumar kondi
– Shivkumar kondi, Commented Dec 11, 2017 at 9:56
SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U' — Tom
– Tom, Commented Dec 11, 2017 at 10:03

Tom · Accepted Answer · 2017-12-11 10:08:59Z

1

There are several ways of getting all tables in your database

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

OR

SELECT * FROM Sys.Tables

OR

SELECT * FROM INFORMATION_SCHEMA.TABLES

OR

SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U'

answered Dec 11, 2017 at 10:08

Tom

1,0172 gold badges15 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Akash Sharma · Accepted Answer · 2018-06-19 15:03:59Z

0

select * from tab

or

select * from * cat

The first one will show the associated CLUSTERID with tables for the current user.

answered Jun 19, 2018 at 15:03

Akash Sharma

931 silver badge12 bronze badges

JonTout · Accepted Answer · 2018-06-19 15:11:42Z

0

I think you need

SELECT name FROM master.sys.databases

and then you will need to create a cursor query (for your USE XXXX) to iterate through the following query.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

answered Jun 19, 2018 at 15:11

JonTout

6406 silver badges15 bronze badges