August 12, 2009 at 3:18 pm
what is the query for retriving the list of the tables in a database.
August 12, 2009 at 3:50 pm
got it.........
select * from sys.tables
thanks....................
August 12, 2009 at 4:22 pm
I might suggest a more portable version:
SELECT * FROM INFORMATION_SCHEMA.TABLES
CEWII
August 14, 2009 at 9:04 am
You can also use sp_tables
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 14, 2009 at 9:19 am
select * from dbo.sysobjects ( not sys.tables)
and you can use TYPE column in where clause to get specific tables like user table,system tables,views etc.
Irfan
August 14, 2009 at 9:27 am
and if you also want to count the number of rows in each table :
sp_msforeachtable 'select count (*) as ''?'' from ?'
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
August 14, 2009 at 10:26 am
Actually, in SQL Server 2005 this may work faster:
select
t.[name] TableName,
t.create_date,
t.modify_date,
sum(p.[rows]) RowCnt
from
sys.tables t
inner join sys.partitions p
on (t.[object_id] = p.[object_id]
and p.index_id in (0,1))
group by
t.[name],
t.create_date,
t.modify_date
order by
t.[name],
t.create_date,
t.modify_date
Avoids the cursor in sp_msforeachtable and if you have thousands of tables the above query will run much faster.
August 19, 2009 at 3:37 pm
Irfan (8/14/2009)
select * from dbo.sysobjects ( not sys.tables)
sysobjects is deprecated in SQL 2005 and will be removed in a future version of the product. Rather use sys.objects, if you want all objects. or sys.tables if you want just user tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply