SQL Server Performance Forum – Threads Archive
Tables
Hello, guys. How do I see how many tables I have in my database? Thanks.select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘BASE TABLE’ select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘BASE TABLE’
Thanks, Chappy for the response. But with these statements I see the information
in system db (pubs,…) In the database that I work with, it shows nothing.
Thanks.
Run SP_HELP to get complete information about that database and in the list you can find the number of tables. Is that user database is an empty shell with no objects created? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Satya, this user database has over 100 tables. Is there any way I can see the actual number of them in there?
Run Chappy’s tip:
select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ‘BASE TABLE’
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Also, make sure your query is set to use the database for which you want to know the number of user tables: USE MyDatabase
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’
Replace "MyDatabase" with the appropriate database name.
SELECT COUNT(*)
FROM SYSOBJECTS
WHERE XTYPE=N’U’
will do the trick, too. I also checked Chappy’s tip — it worked in my case. — Marek ‘chopeen’ Grzenkowicz ‘You’re so cute when you’re frustrated.’ — Interpol
Marek, It’s the same issue with INFORMATION_SCHEMA.TABLES and sysobjects — you get the results for the database that you’re connected to. So if your QA window is looking at the master database, you won’t see any results for MyDatabase. And no, you cannot use MyDatabase.dbo.sysobjects either – you must include USE MyDatabase for these queries to work.
quote:
It’s the same issue with INFORMATION_SCHEMA.TABLES and sysobjects — you get the results for the database that you’re connected to. So if your QA window is looking at the master database, you won’t see any results for MyDatabase.
Are you sure on this?
It’s the same issue with INFORMATION_SCHEMA.TABLES and sysobjects — you get the results for the database that you’re connected to. So if your QA window is looking at the master database, you won’t see any results for MyDatabase.
USE MASTER
GO
SELECT
COUNT(*) FROM pubs.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’
SELECT
COUNT(*) FROM FRANK_PLAYGROUND.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’
SELECT
COUNT(*) FROM master.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘BASE TABLE’ ———–
21 (1 row(s) affected)
———–
11 (1 row(s) affected)
———–
15 (1 row(s) affected)
———————–
–Frank
http://www.insidesql.de
———————–
quote:Originally posted by FrankKalis
Are you sure on this?
———————–
–Frank
http://www.insidesql.de
———————–
———————–
–Frank
http://www.insidesql.de
———————–
Hi Frank, Oops, me bad[:0] – at least for the INFORMATION_SCHEMA objects. As far as I’m aware, it is still true for sysobjects.
quote:Originally posted by Adriaan
It’s the same issue with INFORMATION_SCHEMA.TABLES and sysobjects — you get the results for the database that you’re connected to. So if your QA window is looking at the master database, you won’t see any results for MyDatabase.
And no, you cannot use MyDatabase.dbo.sysobjects either – you must include USE MyDatabase for these queries to work.
I am not sure what you mean. Please explain if you don’t mind.
Did I sound like I couldn’t do something? — Marek ‘chopeen’ Grzenkowicz ‘You’re so cute when you’re frustrated.’ — Interpol
Me bad again … [<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br /><br />Not sure why it was stuck in my head that you should not use MyDatabase.dbo.sysobjects …<br /><br />Just as long as you make sure that you’re either connected to the proper database, or that you use the MyDatabase.dbo.sysobjects syntax.<br /><br />Okay, so where do I go to delete my old messages? This is most embarassing [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />].
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />Me bad again … [<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />]<br />(…)<br />Okay, so where do I go to delete my old messages? This is most embarassing [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />].<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />No problem.<br /><br />My English is far from perfect, so I may be asking questions like this pretty often. Just to make sure, if I understood everything right.<br /><br />–<br /><br />Marek ‘chopeen’ Grzenkowicz<br /><br /><i>’You’re so cute when you’re frustrated.'</i> — Interpol
Hi Marek, By all means, keep on asking those questions. Your English is just fine, if not better than mine.
]]>