0

I wrote this select command, which gives me tables with the associated number of columns from the database.

SELECT
    t.TABLE_NAME,
    (SELECT
      COUNT(*)
    FROM
      INFORMATION_SCHEMA.COLUMNS
    WHERE
      table_name = t.TABLE_NAME) as COLUMNS
FROM INFORMATION_SCHEMA.TABLES t

I have now tried to extend this and to get the rows as well but i fail all the time. There is a solution in a another thread, but I am missing the TABLE_ROWS from INFORMATION_SCHEMA.TABLES. Is there maybe another way to get the rows?

  • 1
    Does this answer your question? [SQL count rows in a table](https://stackoverflow.com/questions/28916917/sql-count-rows-in-a-table) (see [this answer](https://stackoverflow.com/a/28917736/2029983) specifically.) – Thom A Nov 01 '21 at 13:20
  • Yeah, nice. Thanks alot Larnu! Thats what i need. :) –  Nov 01 '21 at 13:28

1 Answers1

1

This works perfect. Thanks @Larnu.

   SELECT
        t.TABLE_NAME,
        (SELECT
          COUNT(*)
        FROM
          INFORMATION_SCHEMA.COLUMNS
        WHERE
          table_name = t.TABLE_NAME) as COLUMNS,
        (select 
            sum([rows])
         from 
            sys.partitions
         where object_id=object_id(t.TABLE_NAME) and index_id in (0,1)) as ROWS
    FROM INFORMATION_SCHEMA.TABLES t