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 Chaya,
you can use the following query.
select *from information_schema.columns where table_name=’ITEM’ order by column_name
Hi
I am new to this SQL.. I want to know how can get the tables from data base..
Can you tell me in a easy way please… I need it as soon as possible to resolve an issue :(
Thanks
Vidhyasri M
Hi Vidhyasri,
you can use any of this query to get all the tables from a particular database
select * from INFORMATION_SCHEMA.TABLES
or
select * from sysobjects where xtype=’U’
or
select * from sys.tables
Regards,
Ambanna Yatnal
Hi Vidhyasri,
you can use:
sp_tables
OR
you can use:
select * from information_schema.tables
OR
You can use:
select * from sys.tables
OR
you can use:
select * from sys.objects where type=’U’
Note: type=’U’ not use xtype
So all these are different way of getting all tables from sql server.
what is Invalid object name ‘sys.tables’. in sql server 2000???
can u plss help me!!!!!!!!!
use this code:
SELECT * FROM INFORMATION_SCHEMA.TABLES
reply by example
how can i display the no of tables present in the database and also displays the table name present in the database
I have Query which requires me to display ‘Active’ or ‘Inactive’ in ‘Status’ Column based on the following Condition:-
IF Todate IS Null or Todate>=Getdate() then status = ‘ACTIVE’
If Todate<Getdate() then status = 'INACTIVE'
Whereas 'Status' is not a column by itself in Agentownership table. Above condition needs to be added in the following query:-
Select AO.OwnerfirstName, AO.OwnerLastName, P.PhoneNumber, AO.FromDate
From AGENTOWNERSHIP AO
Left Join Agent A on AO.AgentID = A.AgentID
Left Join Phone P on P.PhoneiD = AO.PhoneiD
I have the following Columns in my AgentOwnership table:-
AgentOwnershipID
AgentID
OwnershipRoleID
OwnerFirstName
OwnerLastName
PhoneID
AddressID
FromDate
ToDate
OwnerDateofBirth
OwnerDriversLicense
OwnerDriversLicenseStateId
OwnerEmailAddress
Help please! I am still trying to complete the query
@dharma
Look at INFORMATION_SCHEMA.TABLES or sys.tables.
@Chaya
Just add a CASE statement to the query:
CASE
WHEN Todate < Getdate() THEN 'INACTIVE'
ELSE 'ACTIVE'
END Status
hi
In all data bases have a common table ah?
Hi.
i want a SQL querry to list the tables in a schema in DB2. can anyone help me!! Thanks friends!!
It is better you post this question at db2 forum as this site is for SQL Server
Hi,
I m converting my .net application from Mysql to Ms sql
server 2005 but the queries just don’t go well. eg.
MS sql server gives me error (SELECT * FROM Employee).
Thanks
What was the error you got?
Make sure the table exists in the current database your are working with
Hi
I’m using stored procedures to create temp tables with info and use it in SQL reporting services.
Now I have major collation issues making my life difficult.
How can I avoid collation issues in my stored procedures, or how can I change the collation of all tables and all columns in a database.
I get the following error
Msg 468, Level 16, State 9, Procedure BISSystemsOverdue, Line 103
Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.
Hello Bertus,
Different collation of working database and tempdb could cause the collation conflit when using temp tables. To resolve this either specify the collation while creating the temp table or change the default collation of tempdb.
Regards,
Pinal Dave
its very useful info.
Thanks
this is really very explicity information
Hi,
How can I get all the databases on the sever and thier location(file name). i tried too many thing but I couldnt get it.
Thanks
Hi all,
I wanted where condition with two paramters which works in oracle but i wanted to execute in sql server 2000 which gives me error.
Kindly check the query
select * from t1 where (column1,column2) in (select column1,column2 from t2)
i require same query in sql server.If any have ideas on this plz forward to my mail id
thanks and request,
lokesh
Hi Lokesh,
Write as below:
select t1.* from t1 INNER JOIN t2
ON t1.column1 = t2.column1
AND t1.column2 = t2.column2
or another way:
select * from t1
where column1 in (select column1 from t2)
AND column2 in (select column2 from t2)
Regards,
Pinal Dave
How can I get all the databases on the sever and thier location(file name). i tried too many thing but I couldnt get it.