locked
how to query dictionary where database name equal some value RRS feed

  • Question

  • User1868852945 posted

    How can I query the count(*) of tables for a specific database in the local and remote SQL Server instance?

    The oracle equivalent would be:

    local: select count(*) from dba_tables where owner = 'MYUSERID'

    remote:  select count(*) from dba_tables@myDbLink where owner = 'MYUSERID'

    Thanks for any feedback.

    Wednesday, September 13, 2017 4:14 PM

Answers

  • User347430248 posted

    Hi gunderj,

    if you don't want to use "Use Database name" in your query then you can add database name before table name.

    like below.

    SELECT COUNT(*) from demo.information_schema.tables 
    WHERE table_type = 'base table' 
    

    here , demo is the name of the database.

    then if you are not using that database currently then also you will able to fire query and get the count.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 14, 2017 5:15 AM

All replies

  • User347430248 posted

    Hi gunderj,

    to count the number of tables in database you can refer query below.

    Local:

    USE your_database_name
    SELECT COUNT(*) from information_schema.tables 
    WHERE table_type = 'base table' 

    Remote:

    select COUNT(*) from LinkedServerName.DatabaseName.OwnerName.TableName WHERE table_type = 'base table' 

    Regards

    Deepak

    Thursday, September 14, 2017 2:34 AM
  • User1868852945 posted

    Thanks Deepak, but my requirement is how can I count them for a specific database within the SQL Server instance without connecting to that database?  I tried looking in the system database views for a query where I could use something like...

    SELECT COUNT(*) from information_schema.tables 
    WHERE table_type = 'base table' and database_name = 'myDatabse'

    I do not want to use or connect to the database and will need to count the tables for many databases both locally and over linked server.

    Thanks.

    Thursday, September 14, 2017 5:04 AM
  • User347430248 posted

    Hi gunderj,

    if you don't want to use "Use Database name" in your query then you can add database name before table name.

    like below.

    SELECT COUNT(*) from demo.information_schema.tables 
    WHERE table_type = 'base table' 
    

    here , demo is the name of the database.

    then if you are not using that database currently then also you will able to fire query and get the count.

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 14, 2017 5:15 AM