SQL Server Performance

Tables

Discussion in 'T-SQL Performance Tuning for Developers' started by alex, Sep 24, 2004.

  1. alex New Member

    Hello, guys.

    How do I see how many tables I have in my database?

    Thanks.
  2. Chappy New Member

    select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

    select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'
  3. alex New Member

    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.
  4. satya Moderator

    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.
  5. alex New Member

    Satya, this user database has over 100 tables. Is there any way I can see the actual number of them in there?
  6. satya Moderator

    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.
  7. Adriaan New Member

    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.
  8. chopeen Member

    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
  9. Adriaan New Member

    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.
  10. FrankKalis Moderator

    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?


    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
    -----------------------
  11. Adriaan New Member

    quote:Originally posted by FrankKalis

    Are you sure on this?
    -----------------------
    --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.
  12. chopeen Member

    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
  13. Adriaan New Member

    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=':D' />].
  14. chopeen Member

    <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=':D' />].<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
  15. Adriaan New Member

    Hi Marek,

    By all means, keep on asking those questions. Your English is just fine, if not better than mine.

Share This Page