none
How do i find the total number of fields within all tables in one of my databases

    Question

  • I'm in the process of creating some "gee whiz" metrics for one of my applications and want to find the total number of columns contained in its database.  Is there a stored procedure to get this information that would save me the effort of opening each of my 66 tables and counting the columns?  I also have the same question for my 138 views.  Thank you for any ideas you care to offer...............Phil Hoop

    Phil Hoop

    Friday, January 17, 2014 2:01 AM

Answers

All replies

  • Hi Phip,

    Refer the given URL : http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/


    If you think my suggestion is useful, please rate it as helpful.

    If it has helped you to resolve the problem, please Mark it as Answer.

    Varinder Sandhu www.varindersandhu.in

    • Marked as answer by Phil Hoop Friday, January 17, 2014 7:07 AM
    Friday, January 17, 2014 6:50 AM
  • Varinder Sandhu   Thank you.  Any ideas for the views? ...........................Phil Hoop

    Phil Hoop

    Friday, January 17, 2014 7:08 AM
  • Hi Phil,

    Assumes SQL 2005 or higher

    Let 's try simple one ....
    SELECT COUNT(col.column_name), col.table_name
    FROM information_schema.columns col
      JOIN information_schema.tables tbl 
           ON tbl.table_name = col.table_name 
              AND tbl.table_schema = col.table_schema
              AND tbl.table_catalog = col.table_catalog
              AND tbl.table_type = 'VIEW'
    GROUP BY col.table_name

    Let me know if this will help you.


    If you think my suggestion is useful, please rate it as helpful.

    If it has helped you to resolve the problem, please Mark it as Answer.

    Varinder Sandhu www.varindersandhu.in

    Friday, January 17, 2014 7:41 AM
  • you can do as simple as this

    SELECT COUNT(col.column_name), tbl.Table_type
    FROM information_schema.columns col
      JOIN information_schema.tables tbl 
           ON tbl.table_name = col.table_name 
              AND tbl.table_schema = col.table_schema
              AND tbl.table_catalog = col.table_catalog
    GROUP BY tbl.Table_type

    to get them in same resultset

    information_schema.tables have details about tables and views in the database


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, January 17, 2014 8:15 AM