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?

Improve this question
2
  • 1
    Does this answer your question? SQL count rows in a table (see this answer specifically.) Commented Nov 1, 2021 at 13:20
  • Yeah, nice. Thanks alot Larnu! Thats what i need. :) Commented Nov 1, 2021 at 13:28

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Use sys.tables rathre than INFORMATION_SCHEMA.TABLES
Oh thx. Now I no longer have to exclude the views extra. :)