Listing Tables in SQL Server 7 
Author Message
 Listing Tables in SQL Server 7
Hi, I want to write a query to list all tables in a
particular database having 2 columns

Name | No. of Rows.

Thanks.



Wed, 22 Sep 2004 01:05:19 GMT
 Listing Tables in SQL Server 7

Select O.Name, I.rows
From sysobjects O Inner Join sysindexes I On O.ID = I.ID
        AND O.Name = I.Name
Where o.xtype = 'u'

Daniel Wilson, BSCS, MCSE
CompuSoft Solutions and The Worthwhile Company
www.worthwhile.com
Your complete e-business solution partners.
Phone: 864-233-9029 Fax: 509-757-5264

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 22 Sep 2004 01:22:30 GMT
 Listing Tables in SQL Server 7
That does not give me the right answer. Lot of tables are
missing, especially tables with indexes, I think.

Quote:
>-----Original Message-----
>Select O.Name, I.rows
>From sysobjects O Inner Join sysindexes I On O.ID = I.ID
>    AND O.Name = I.Name
>Where o.xtype = 'u'

>Daniel Wilson, BSCS, MCSE
>CompuSoft Solutions and The Worthwhile Company
>www.worthwhile.com
>Your complete e-business solution partners.
>Phone: 864-233-9029 Fax: 509-757-5264

>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.



Wed, 22 Sep 2004 01:31:32 GMT
 Listing Tables in SQL Server 7
I assume you meant you want 2 columns in your result, not that you wanted to
limit your query to include ONLY tables that have EXACTLY two columns in
their definition.  If so:

SELECT
    [Name] = so.name,
    [No. of Rows] = 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
    so.name

www.aspfaq.com


Quote:
> Hi, I want to write a query to list all tables in a
> particular database having 2 columns

> Name | No. of Rows.

> Thanks.



Wed, 22 Sep 2004 01:34:20 GMT
 Listing Tables in SQL Server 7
Quote:
>>That does not give me the right answer. Lot of tables are

missing, especially tables with indexes, I think.<<

Sorry ... guess I posted buggy code on that. I hadn't tested it
rigorously enough.  Aaron's presumably is better.

Daniel Wilson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 22 Sep 2004 01:54:35 GMT
 Listing Tables in SQL Server 7
Note that this list will include the table dtproperties, which isn't in the
regular "Tables" view in Enterprise Manager.

www.aspfaq.com



Wed, 22 Sep 2004 01:55:39 GMT
 Listing Tables in SQL Server 7
I think I am missing some tables when I run the query. Can
you please double check it again? Thanks.

Quote:
>-----Original Message-----
>Note that this list will include the table dtproperties,
which isn't in the
>regular "Tables" view in Enterprise Manager.

>www.aspfaq.com

>.



Wed, 22 Sep 2004 02:39:50 GMT
 Listing Tables in SQL Server 7

Here's some code that combines uses some of what Aaron posted, but does
not use the function he uses ... this code is checking out on the DB's I
test it on.

Select O.Name, Max(I.rows) AS NumRows
From sysobjects O Left Join sysindexes I On O.ID = I.ID
Where o.xtype = 'u'
Group By O.Name

--
Daniel Wilson, BSCS, MCSE
CompuSoft Solutions and The Worthwhile Company
www.worthwhile.com
Your complete e-business solution partners.
Phone: 864-233-9029 Fax: 509-757-5264

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Wed, 22 Sep 2004 02:54:33 GMT
 Listing Tables in SQL Server 7
I did double check it.  What tables are you missing?  System tables?  It
should capture all user tables.  I verified against 7.0 and 2000.

www.aspfaq.com


Quote:
> I think I am missing some tables when I run the query. Can
> you please double check it again? Thanks.



Wed, 22 Sep 2004 03:00:53 GMT
 Listing Tables in SQL Server 7
FWIW, on my systems, the two queries return the exact same results.  I would
guess that Dipen expects it to return something other than user tables
(local/global #temp tables, system tables, tables in another
database/server/network/galaxy, ...)?

www.aspfaq.com


Quote:

> Here's some code that combines uses some of what Aaron posted, but does
> not use the function he uses ... this code is checking out on the DB's I
> test it on.

> Select O.Name, Max(I.rows) AS NumRows
> From sysobjects O Left Join sysindexes I On O.ID = I.ID
> Where o.xtype = 'u'
> Group By O.Name



Wed, 22 Sep 2004 03:26:31 GMT
 Listing Tables in SQL Server 7
Sorry, seems ok to me. I got another query from a
different newsgroup. It said:

select so.name as Name, si.rows as Rows
from sysindexes si
inner join sysobjects so
on so.id = si.id
where so.type = 'U' and si.indid in (0,1)
order by so.name

Now, I don't understand what is the diff. between your
query and the above query. Both queries result same
results. Any explainations would be appreciated. thanks.

Quote:
>-----Original Message-----
>I did double check it.  What tables are you missing?  
System tables?  It
>should capture all user tables.  I verified against 7.0
and 2000.

>www.aspfaq.com



>> I think I am missing some tables when I run the query.
Can
>> you please double check it again? Thanks.

>.



Wed, 22 Sep 2004 04:18:38 GMT
 
 [ 11 post ] 

 Relevant Pages 

1. To list Tables in SQL Server 7.0

2. SQL Server FAQ, SMS discussion List, SQL Server Discussion List

3. Here: SQL Server FAQ, SQL List Server (discussion list) and online books

4. List SQL Server Servers, Databases and Tables

5. Getting a list of Tables using Borland C++ Builder 5/MS SQL Server 8

6. List of Tables in SQL Server DB

7. Retrieve table list from SQL Server.

8. How to get a list of Tables in a SQL Server 7.0 Database

9. Bug in SQL Server when using table alias with column list

10. reading list of tables using ODBC driver for SQL server 7.0

11. [ODBC SQL Server Driver]Degree of derived table does not match column list

12. SQL Server not showing in Server List


 
Powered by phpBB® Forum Software