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
Thanks for your help but i am looking something like where i can display the list of database . Please anybody help me to listing of database in sql 2005.
sp_helpdb
USE THIS CODE AND YOU WILL FIND ALL THE DATABASES.
Hi,
Thanks this helped me.
can anyone tell me how to see only the user defined tables or
stored procedures or views or functions in a given database ?
1) select distinct type from sysobjects.
2) using for user table use
select distinct type from sysobjects type=’u’
for procedures type=’p’ etc….
Thanks
Thanks a lot!!!!!!!!!!!!!!
dear sir,
i want to know how to see the structure of table……..
In oracle desc tablename means it will show the structure of that particular table but the sql server it is not show ………
i need solution for this question as early as possible …..
advance thanks ………. please consider as a urgent…..
Just drag the table into query pane of SSMS and select the table and click Alt+F1 and it will displays the structure of the table.
—Bobbili Venkateswara Rao
Also refer this post to know various methods
SP_HELP TABLE_NAME … YOU WILL GET THE STRUCTURE OF THE TABLE
Suresh,
to view the structure of the table in MS SQL Server
try this
Solution: sp_help tableName
Thanks,
Venky
and how you can see the structure of all tables of a specific database?
thanks in advance for your reply
I want to see all the data in all the tables in a database using the least code possible !!! Please let me know ASAP…
Why do you want to do this?
That doesn’t make any sense
how many user using my db
(same user) ?
You can run
EXEC sp_who
to know it
Thanks!
thaink you
what about
only user tables?
select * from information_schema.tables
where table_type='BASE TABLE'
I got much information regarding the database list of objects
Thank you
Harinath
I am not getting abt master table.
Thank you. Helps a lot :)
How could i get all the data from all the table of the specific database?
Thanks a lot in advance!
This will generate the query for you
select ‘select * from ‘+table_name from information_schema.tables
where table_type=’BASE TABLE’
Querry to fetch all tables in a database:
select * from sysobjects where type=’u’
————————————————–
Querry to fetch all procedures in a database:
select * from sysobjects where type=’p’
Regards
Rap…
to view all tables……
SELECT * FROM TAB;
this will work…….
This will only work in ORACLE. But this site is specific to MS SQL Server
This will work in sql or oracle not in sql server.
Dear sir,
How do i get all databases in sql server
Regard’s
pushparaj
EXEC sp_databases
Dear Friends,
I want to know how to get all table name from particular data base in sql server 2005.
Thanks.
u can get all the tables by using the comman
select * from sys.objects where type=’u’ (in sql server)
and
select * from tab (in oracle)
Hi ,
I am creating an application where I have to list all the servers and on selecting any server the combobox should dynamically have all the databases that are present on that server so that the user can select any one of them. Can anyone please help. its urgent!!!
thanks,
Gaurav
You can query the view sys.servers
@Kaushal
I want to know how to get all table name from particular data base in sql server 2005.
select * from sysobjects where xtype=’U’
if i want to check the primary and foreign key relations of particular table is it possible….if there is any solution plz tell me……..
thx in advance
Regards,
Rekha…
Primary key informations
EXEC sp_pkeys ‘table name’
Foreign key informations
EXEC sp_fkeys ‘table name’
I need to display only user defined tables from the current database.