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,
i have 400 tables in a database. I have a requirement which need exhaustive document showing all the dependencies and information about all tables in a database(user tables). Please let me know if there is anyway to do this.
you can see all table information by
select * from sys.tables
or
select * from infomation_schema.tables
or
sp_tables
you can see all procedures by:
select * from sys.objects where type=’p’
you can see all databases by:
sp_databases
you can see all indexes by:
select * from sys.indexes
How to get tables from sqldatabase
Dear sir,
i want to know how to see the structure of all tables…..
sp_help tablename gives the output only for 1 table..i have 200 tables in my db.. should i hit the query for each table.. or is there any other solutuion for it..
plz reply…
thanx in advance.
Make use of “Generate SQL script” option from Management studio
Hi Suyog,
You can see the structure of all tables. by using….
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in(select TABLE_NAME from INFORMATION_SCHEMA.TABLES)
Excelent answer,
Thanks Sourabh Sachdeva!
Hi Sourabh,
UR answer is ohkk.. but i need INDEX constaints also in it.. so wat should i do??
plz reply..
Great man!
Dear Sir,
that query is not showing me index ,constraints,columns details…
is there any other way..except sp_help tablename..
can we go for sp_msforeachtable???
can u plz provide exact query for same..
hi suyog,
If you want to see structure of all tables you can use:
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME in(select TABLE_NAME from INFORMATION_SCHEMA.TABLES)
If you want to see index details then you can use:
select name,type_desc from sys.objects where type=’PK’
Hi suyog,
Not a proper way but i think u can use
declare @tableName as varchar(100)
declare @countValues as int
select @tableName=name from sys.tables
select @countValues=COUNT(*) from sys.tables
declare @startvalue as int
set @startvalue=1
while @startvalue<@countValues
begin
declare @exe as varchar(100)
set @exe='sp_help '+@tableName+''
print @exe
exec(@exe)
set @startvalue=@startvalue+1
end
after executing these statements you can see all tables structure info. If you want to combine all data into single resultset you can use temporary tables etc..
you can use this,
sp_msforeachtable ‘sp_help [?]’
Appreciate your blog. Does anyone have any ideas on how to lookup the source table/column associated with a column in a given view? I’m looking for a way to map a column from a view (that shows up in syscolumns) back to the originating table and column that the view column is based on.
how do i know the structure of table like in mysql or oracle viz desc.i uses sp_privileges but it return the result that i dont want. i want to know the data type and its size
zee
Select * from table name
where Fname like ‘%a%’ and Fname like ‘_li’
i hope you like it
zee
Select RNo+2 As RNo,FName From DBtab
Where FName=’lia’
Thanks Sir…
Big help
select * from sysobjects where xtype=’U’
How to find the number of records from all the tables in sql 2005?
There are many methods to do this
Refer this blog post to know more about it
Hi Lakshmi,
I think there is no any build in table or any built in function in sql server through you can count number of records in all the tables.
But i have created a logic through you can get number of records from all tables….
You can use this:
declare @dynamicqry as varchar(1000)
create table temp(tid int)
declare @startvalue as int
declare @endvalue as int
set @startvalue=1
select @endvalue=count(*) from INFORMATION_SCHEMA.TABLES
while @startvalue<@endvalue
begin
declare @tablename as varchar(100)
select @tablename=Table_name from (select Table_name,ROW_NUMBER() over(order by table_name asc) 'RowNum' from information_schema.tables) tt where RowNum=@startvalue
set @dynamicqry='insert temp select count(*) from '+@tablename
print @dynamicqry
exec(@dynamicqry)
set @startvalue+=1
end
select SUM(tid) from temp
Hello,
I tried to run the following query for display columnames in Ascending order from table Item:-
exec sp_columns Item order by Column_name Asc
I get the following error:-
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘order’.
How shhould I construct the query in order to get the desired results?