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 Pinal
Want to know how do I do database versioning? Actually I want to achieve something like application versioning, so when the application starts it should detect database version and compatibility with the application version. If the database version is compatible with application version then application wil continue, but if not then it should ask for the compatible database version.
Thanks
Shailesh S Bhute
You can have a table to store these informations with dates. Check for the version with the latest date
i tried so many queries to list all the tables i have created,and at the end this simple query did the magic!!
select * from Tab;
lists all the tables under the logged in user…
Note that this site is for MS SQL Server and the query you posted will only work in ORACLE
Thanx for help
use DataBaseName
Go
select * from sys.tables
Go
What is Magic Table ??how can see its data using select.
It is usually a table created inside a trigger (inserted, deleted)
thnak you very much
The best artcle at any time ! great name great explanation and the website very interesting !
Hi Pinal, Madhivanan
I have a database with 1000 tables (for each table _id is the primary key).
Would it be possible to select all tables which are having _id > 1500 (i.e Primary Key value > 1500).
Thanks in advance,
Sri.
Run this
select ‘ select * from ‘+table_name+’ where _id>1500′ from INFORMATION_SCHEMA.tables
Copy the result and run it
Thank you Madivanan.
I did the similar thing in the fist place, but I did have a thought to check if is there any elegant way to do this without need of running those queries.
SELECT * FROM SYS.TABLES
then loop through all the tables to check if the table meets the criteria.
SELECT COUNT(*) FROM [] WHERE Table_id >1500 HAVING COUNT(*) > 0
then appended all the table names to a list (tables which return a value) .
Coming to your query:
select ‘ select * from ‘+table_name+’ where _id>1500′ from INFORMATION_SCHEMA.tables
only composes the SQL like :
select * from employee where employee_id>1500
but it not tell us if the table has meet our criteria?
again I need to check if the query returned any rows etc., in program.
Please let me know if is there any better approach. Many Thanks for your reply.
Sri.
Ok. Execute this code
declare @sql varchar(max)
set @sql=”
set @sql=@sql+’ select * from ”’+table_name+”’ where _id>1500”’ from INFORMATION_SCHEMA.tables
exec(@sql)
It will list out required data from all the tables
Thanks for your reply and patience.
I am having trouble in executing the above query.
I have replaced ’ with single quote, but it is erroring with a messgae : Incorrect syntax near the keyword ‘from’.
(tablename_id is the primary key rather than just the _id)
Could you please advise?
Thanks,
Sri.
There is an extra single quote. Try this
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ select * from ‘+table_name+’ where _id>1500′ from INFORMATION_SCHEMA.tables
exec(@sql)
Thank you Madhivanan, this is working fine now.
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ select * from [‘+table_name+’] where [‘+table_name+’_id]>1500′ from INFORMATION_SCHEMA.tables where table_type=’BASE TABLE’
exec(@sql)
But I wonder if is there any way to list only the table names not the tables data (tables which are having rows with id> 1500)?
Thanks,
Sathish.
Try this code
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ if exists(select * from [‘+table_name+’] where [‘+table_name+’_id]>1500) select top 1 ”’+[table_name]+”” from INFORMATION_SCHEMA.tables where table_type=’BASE TABLE’
exec(@sql)
Thank you Madhivanan, This SQL is working fine.
Many Thanks for your patience.
You are welcome Sri. Thanks for the feedback
i have a problem with a step in my sql databse which is as the following:
– Create a Stored Procedure that copies the record(s) in tblDAT_MyStar into tblDAT_MyStartHistory every time the stored procedure is called. The example is given here to help you out. In your code, make sure that the value returned by GetDate() is assigned to a local variable first. That is, you need to declare a local variable of type that is suitable of storing a date together with time. Populate the header section with your info.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: ****
— Create date: 1/18/2011
— Description: Show an example of SP and copying records
— =============================================
create procedure [sp_CopyingClass]
as
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;
declare @Status as int
— Insert statements for procedure here
Begin transaction
INSERT INTO [dbo].[tblDAT_ClassHistory]
([ClassCode]
,[InsertedAt])
Select Classcode, GETDATE() from tblDAT_Class
select @Status = @@ERROR
if @Status = 0
commit
else
rollback
return @Status
END
GO
the tables are:
– tblDAT_MyStar
column name
FirstName nvarchar(128)
LastName nvarchar(128)
john nchar(10)
– tblDAT_MyStartHistory
column name
ID_AA21080 int
FirstName nchar(10)
LastName nchar(10)
InsertedAt nchar(10)
please help
hello sir
i knew sql server 2005 i knew create tables and backup and maintain column and row . but i have no exp in database field. i don’t know about use of sql server in mnc companies. i dont know use full text search and stored proceudre and triggers
what should i do in database file
plz help me this query
becoz its very important for my life
happy
Have you searched for them in SQL Server help file?
Hi every one!
how can i view all the tables in my database on my admin page. i tried
SELECT * FROM INFORMATION_SCHEMA.TABLES
it gives me “Resource id #3” and doesn’t show any thing on the page.
pliz help.
Hi All,
I want the list of all tables and views of the database which has “emailaddress” as a field.
How can i achieve this?? Please throw some light..
Regards,
Sneha
select table_name from information_schema.columns
where column_name=’emailaddress’
Hi ,
Query:
1) Any giving user id give all their business that have been in active state by admin1
============
2)List all the posts under top category Science
=========
3) All the address under country Singapore..
please reply to my mail ASAP thanks
Raj
[email removed]
What have you tried so far?
hey,
anyone can help me with this problem,
i need to count all numeric type collum of each db table
select table_name,COUNT(COLUMN_NAME) from information_schema.columns
where NUMERIC_PRECISION is not null
group by table_name
thanks for help
how i can get the list of all database on the system
use this
exec sp_databases
Can anyone suggest me any tool or agent through that
i can pull out a system hardware information means asset scanning & insert the data into a sql database.
Hello Sir,
If i want join the Sys.Databases and Sys.Tables how can i do?
Dear,
i am using ” Toad for Oracle” and i went to fetch all tabels is the database and i can’t use the below quary due to the below error.
“table or view does not exist”
please advice
Note that this site is for MS SQL Server
I need to create history tables based on their orginating tables. The only exception being I need a new primary key field and the primary key field from the originating table needs to become a regular numeric field. I know comething can be created using sql Server 2005 sys.tables, sys.columns, etc. Has anyone done this? Can you offer up some sql to help me along my way?