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
Hi Tonny,
U will get the user defined tables using any query below.
select * from sys.tables
select * from INFORMATION_SCHEMA.TABLES
select * from sys.objects WHERE type =’U’
Hi everyone, I am new here.
I am trying to get a list of all of the tables in a DB and the size/# of rows in each table.
Does anyone know how to get this data?
Thanks in Advance for your help!
dear sir,
i want to diplay all databases name in sql2000.
@ahila
use master
Select name from sysdatabases
hope this helps,
Imran
how i can see all tables in sqlserver2000;
thanks..
One method is
EXEC sp_tables
/*
USE YourDBName
GO
SELECT *
FROM sys.Tables
*/
i used the above code but shows error stating
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.tables’.
is it dude to any syntax error
???
This will only work from version 2005 onwards. If you use version prior to 2005, use systables
USE mrpoteat SELECT name FROM sys.Tables where name = name and phone = phone
I get error
Invalid column name ‘phone’.
Invalid column name ‘phone’.
Invalid column name ‘phone’.
Im using SQL Server 05
What do you want to get as the output?
Try this
USE mrpoteat SELECT name FROM sys.Tables where name = ‘name’
@dumdum
Script1 :This will list all the system tables and userdefined tables in the database. ( meaning all the tables in the database)
use master
create table #temp1 (id int identity , name)
insert
SELECT name FROM sysobjects where type = ‘S’ or type = ‘U’
Script2: If you want to list all the system and user defined tables in all the database at a time, then you can use this script,
I am sure there must be a another good way of doing this,
use master
create table #temp1 (id int identity , name varchar(128))
go
create table #temp2 ( table_name varchar(128), db_name varchar(128) )
go
insert into #temp1
SELECT name FROM sysdatabases
go
declare @var int
declare @db_name varchar(128)
declare @cmd varchar(1280)
set @var = 1
while @var <= (select count (1) from #temp1)
begin
select @db_name = name from #temp1 where id = @var
set @cmd = ‘SELECT name , db_name = ‘+””+@db_name+””+’ FROM ‘+ @db_name+’..sysobjects where type = ”s”or type = ”U”’
insert into #temp2 exec (@cmd)
set @var = @var+1
end
select table_name As ‘Table Name’ , db_name As ‘Database Name’ from #temp2 where table_name not like ‘#temp1%’ and table_name not like ‘#temp2%’
drop table #temp1
drop table #temp2
go
Hope this helps,
Thanks,
@martin joseph
You can execute that query in Sql Server2005, it will give you all userdefined tables. Your query is absolutely correct.
In Sql Server 2000 I dont think we have an object by name sys.tables that is why we canno use that query and that is why it is returning errors.
There is something similar if you might want to use it, its sp_tables, it will list all the tables and views along with some good information in the particular database.
Hope this helps,
Imran.
Can I retrieve all tables from all database in SQL Server?
If yes please give me the query.
select * from sysobjects will give you all the tables in the database.
Note that you need to apply the fileter where xtype=’u’ for tables. Otherwise all objects will be listed
Can I retrieve all tables from all database in SQL Server?
If yes please give me the query
Can anyone please tell me how to get all the tables which has same column name in a database
select table_name from information_schema.columns
where column_name='your_col'
thanx imran for ur reply…
it was very helpful … i might have wasted a lot of time
using the code in ” sql server 2000″
Hi friends,
these are also some use full
please check
Querry to fetch all user tables in a database:
use yourdbname
select * from sysobjects where type=’s’ order by name
Querry to fetch all system tables in a database:
select * from sysobjects where type=’U’ order by name
Querry to fetch all procedures in a database:
select * from sysobjects where type=’p’
Querry to fetch all functions(userdefined or system) in a database:
select * from sysobjects where type=’fn’
Cheers,
Venkat
Hi friends,
This is very use full for finding the size, table, find …
query:
exec sp_spaceused [tablename]
result:
name rows reserved data index_size unused
tablename 145 888 KB 248 KB 16 KB 624 KB
Cheers,
venkat919
Hi Friends,
After i did the exercise
to get the all user tables from database:
— this will gives the one extra table it is “dtproperties”
1. select * from sysobjects where type=’U’ order by name
— this will gives the exact user tables from database
2.
SELECT * FROM sysobjects
WHERE
type=’U’
and
name != ‘dtproperties’
ORDER BY [name]
Cheers,
Venkat
Dear Friends
How To Find Particular Table stay Which Database ?
I want to build an if statement that will create a table with different information in it based on the database that is currently in USE (the database name in the USE statement). Different databases USE the same table but have different information in them.
Hi Pinal,
i want to fetch all databases and tables from sql server database, mysql database, oracle database and ms-access database. can u suggest me how to do this. if possible post an example for each. i want to bind all those details to a treeview in asp.net.
Thanks in Advance,
Santosh.
Does anyone have any thoughts on how to accomplish the following? I would like to query a SQL2005 db and return the table and column that contains a particular value. Based on what I have read from this site – this seems to be a good start. SELECT * FROM information_schema.Tables
I would like to add syntax that will find a varchar value and return the table, column, and value itself.
Thanks for the help.
Thanks !