Reading the number of tables in a database 2

[tztexas]

I was sent here from the MS Access forum. Hope this is where I should be. (my statements are within quotes, the others are the answers I received.

"I can query a specific table, however I want to be able just to know how many tables are in a specific database.
Any ideas? thanks"


select count(*)
from msysobjects
where left(name,3)<>"msy"
and left(name,1)<>"~"
and type = 1;

This you should be able to paste directly into the sql view of a query (last one had one closing parens to much, and missed temp tables, as mentioned by FancyPrairie). Access will probably add some [brackets]. Msysobjects is one of the system tables that is normally not visible in the database. In tools | options - view tab, check System Tables, to be able to see them. Msysobjects contains information on all the main objects of the database.


"I tried Roy's and PHV's code, and I get the same error on both scripts.

msg 208 - Invalid object name 'msysobjects'

I'm running this from the SQL Query Analyzer"


So don't ask in an Access forum but in a SQL Server one ...


"thanks

 

[Pattycake245]

Take the 'm' off of the sysobjects name.

Tim

 

[vongrunt]

And sysobjects type is char(2) column. Use type='U' for user tables instead.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[tztexas]

I tried this

select count(*)
from sysobjects
where left(name,3)<>"msy"
and left(name,1)<>"~"
and type = 1;

I get Invalid column name 'msy' and
Invalid column name '~'

Using the SQL Query Analyzer
In my Database display in the left pane, I have
MYIDB
User Tables
dbo.ALL_CASES
etc...

 

[Pattycake245]

Take the double quotes off and make them singles. When you use double quotes QA interprets those as column name references.

Tim

 

[tztexas]

Thanks,
the single quotes and the 'U' type made it work.
Thanks