This is very simple and can be achieved using system table sys.tables.
USE YourDBName
GO
SELECT *
FROM sys.Tables
GO
This will return all the tables in the database which user have created.
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
448 Comments. Leave new
How do I list all tables on a server?
select * from sys.tables
(or)
–list of all user defined tables
select * from sysobjects where xtype=’u’
sp_msforeachdb @command1=’USE ?;SELECT * FROM sys.Tables’
sp_msforeachdb @command1=’USE ?;SELECT * FROM sys.Tables’
For the above query i am getting this message. Kindly help me
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘’’.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘’’.
Use a single quote
To remove this error you have to do just simple thing. Just do that I say and run it..
‘use’: firstly remove this single ‘ ‘ and then add with yourself to this ‘ ‘; Because when you copy this and try to run this error will occur.
select * from sys.tables
error: Invalid object name ‘sys.tables’.
i use 2000 sql serv.
sir use to select * from employee,salary
this is the right query
will u plztell me the queury by which it onlys shows yser defined database
select * from sys.databases where database_id>4
in your connection string set your Initial Catalog to your selected database and then set your command string to: select * from sys.tables
Mr. happy select * from …. is right query. But it will show all query table combinly
Correct query for this syntex in 2000 sql server
select * from sysobjects where xtype=’u’
if u have any doubts pls contact me
woah, that’s working men, thanks a lot ..
Ok this works but i dont want to get it from just the test table I want to get it from all the tables that I create
“USE mrpoteat SELECT name, phone FROM mrpoteat.dbo.test where name = name and phone = phone”
How to identify the ipaddress and user who has created the table
How do I list all databases on a server?
use master
select * from sysdatabases
or
EXEC sp_databases
Hey guys, I know that Pinal is pretty busy, so I thought I would help you out with your questions.
To get all the databases on a server, you can use the same technique, but just a bit different query.
USE Master
GO
SELECT *
FROM sys.Databases
GO
For a list of all tables on a server, I would use the sp_msforeachdb procedure like this.
sp_msforeachdb @command1=’USE ?;SELECT * FROM sys.Tables’
You can see some more examples of sp_msforeachdb on my own blog, here is a link to the article.
Sp_helpdb is used to List All Databases
SELECT *
FROM information_schema.Tables
This will display the list of table in the database
thnks for query on sqlauthority
I Think it is not working in SQL SERVER 2005.
Hello Ram,
Its working. INFORMATION_SCHEMA views are SQL-92 ANSI standard views and will work in future versions also. This view returns only the user tables on which you have SELECT permission. Let us know the issue you are facing.
Regards,
Pinal Dave
you can use following to show all dbases
“sp_helpdb”
thanks
i got it
but
select * from sys.tables
error: Invalid object name ’sys.tables’.
i use 2000 sql serv.
In SQL Server 2000, use
select * from sysobjects
where xtype=’u’
order by name
thank you sir i got it but i also need for show all database in particullar website and this add to dropdown listbox
Try this ………
Exec sp_MSforeachdb ‘Select * From ?..sysobjects where xtype= ”U”’
Sorry for viewing all db,
here is the query ….
select * from sysdatabases (use it in master db)
for viewing all tables in all db … use the above…
Hi
Thank’ sir
THANK YOU RADHA FOR THIS QUERY IF IT IS POSSIBLE TO VIEW LINKSERVER TABLES?
MAYUR
How could i get all the data from all the table of the specific database..?
plz reply on my mail..if possible.
thanks in advance
Bhuwanesh..
I want to view all the table in a database
USE
SELECT *
FROM information_schema.Tables where TABLE_TYPE=’BASE TABLE’
SHOW TABLES FROM (“DATABASE NAME” add your database name here)
Note that this site is for MS SQL Server and not for mysql
hi,
i want to see all the table names from a sigle database in sql server 2000. please anyone can help me!!!!!!!!!!!!!!!!!!!
Have you tried this?
select * from sysobjects
where name like ‘sys%’
order by name
SYS. naming convention will only work with SQL 2005 and SQL 2008.
For sql 2000 you need to filter the objects system table
for example if you want to see all user sepcific tables
select * from sysobjects where type=’U’
You can check BOL for SQL 2000 to identify various types.
i want to see the in second highest salary in a table sql server 2005 data base
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=’BASE TABLE’
Thank you
how can i know that any changes is occur in my table structure. is there any field in any system table that value is change if any changes is done in table structure???????
please give me reply……..
thanks…………..
You can audit only thru a DDL trigger from version 2005 onwards
i can not see systables in my database please tell me how i can see and show systables
Have you tried this?
select * from sysobjects
where name like ’sys%’
order by name
select * from sys.tables
I want to view all the table in a database
select * from sys.table
hi,
how to take the data to all back up in .sql with data also,
example my sql data base so use export command like it show the sql format also in the same i need the mssqlserver 2005 database back up to all data with quires
How do i list all database triggers only in an instance?
Hi,
Which query i used to list the tables in Particular Database?
select * from sysobjects
where xtype=’tr’
order by name
Thank U……..
Regards,
Mani……..
i want to see all tables in sqlserver2000;
i want to see all tables in sqlserver2000; given queries are not working im my system;
What was the error you got?
Thank you…very much…its very helpful …
Thankyou & Regards,
Princes
Works like a dream!! Thank You
Thanks for ur help in viewing all tables from a database. Can u tell me how to view the table structure ?..
EXEC sp_help ‘table name’