Help! Problem with query to list all database tables

• barbararyan

  SSC Veteran

  Points: 221

  More actions

  September 29, 2009 at 6:59 am

  #142258

  When I execute the following query (to list all tables in a database) for any of my SQL server databases, the result set contains 6 rows (see below). What am I doing wrong? Thanks!

  SELECT

  [TableName] = so.name,

  [RowCount] = MAX(si.rows)

  FROM

  sysobjects so,

  sysindexes si

  WHERE

  so.xtype = 'U'

  AND

  si.id = OBJECT_ID(so.name)

  GROUP BY

  so.name

  ORDER BY

  1 asc

  TableNameRowCount

  MSreplication_options3

  spt_fallback_db0

  spt_fallback_dev0

  spt_fallback_usg0

  spt_monitor1

  spt_values2506

• Sergey Vavinskiy

  SSCarpal Tunnel

  Points: 4469

  More actions

  September 29, 2009 at 7:40 am

  #1059472

  That is because you are running your script against master database, but not against your database.

  In the beginng of your script you have to use

  USE [YourDB_name]

  GO

  There is more simple way to get the list of all tables in any database::-)

  USE [YourDB_name]

  GO

  SELECT * FROM sys.Tables

  ORDER BY [name]

  GO

• barbararyan

  SSC Veteran

  Points: 221

  More actions

  September 29, 2009 at 7:45 am

  #1059475

  That works - Thanks!!!