0

I'm currently try to connect the tables I have in my database (who's based in "Microsoft SQL Server Management Studio") to a Excel file.

I've already connected the name of all the tables, but what I need to do is displaying how many items there are in every single table.

Like this:

example

I did it via

SELECT COUNT(*) 
FROM [DB_name].[dbo].[DB_table]

My question is: is there a way to SELECT COUNT (*) for every table in my database with just one SQL command?

I need something like:

SELECT COUNT (*)
FROM [MEC-ED].[dbo].[all_Tables]
aim0d
  • 129
  • 7
  • 2
    You can get the *approximate* row count from the system tables. You can't do a `COUNT(*) FROM {Every table}` though, no; you would need 1 `SELECT` statement per table. – Thom A Mar 03 '22 at 10:04
  • Okay, as i thought, thnks. Mind me an other question (and if u need example i'm gonna edit the main question) – aim0d Mar 03 '22 at 10:06
  • If i need to add a new DB_Table, my list in Excel update automatically , but row count doesnt update. Do i have to add a SELECT COUNT (*) manually? or is there a way to add it automatically as the Table_Name? – aim0d Mar 03 '22 at 10:08
  • 1
    I have no idea what the above question has to do about Excel; you're asking about SQL Server here. – Thom A Mar 03 '22 at 10:10
  • You can add Query command in excel, so i'm asking for excel. Also i added both of the tags so i'm asking for the both of them – aim0d Mar 03 '22 at 10:14
  • How to get the count for *every* table in a database, and how to consume data from SQL Server in Excel are *completely* different questions, and should be asked as 2 separate questions. – Thom A Mar 03 '22 at 10:16
  • That come out wrong, i dont know how to express what i need (english is not my first language), so nevermind thanks for the first answer you gave me. – aim0d Mar 03 '22 at 10:19

1 Answers1

2

Try this

SELECT      o.name          AS TableName
            , i.rows        AS NumRows
FROM        dbo.sysobjects o
JOIN        dbo.sysindexes i
ON          o.id = i.id
WHERE       i.indid < 2 
            AND o.type='U'

You might want to run this first

DBCC UPDATEUSAGE(0) WITH NO_INFOMSGS

. . . to get more accurate results.

wqw
  • 11,771
  • 1
  • 33
  • 41