none
Stored procedure to find number of tables in database and no of columns in each table

    Question

  • IS there any STORED PROCEDURE to find how many table  are there in each database and how many columns in each table????
    Monday, March 29, 2010 6:56 AM

Answers

  • you can query INFORMATION_SCHEMA

    select    *
    from    INFORMATION_SCHEMA.TABLES
    where    TABLE_TYPE    = 'BASE TABLE'
    
    select    *
    from    INFORMATION_SCHEMA.COLUMNS


    KH Tan
    Monday, March 29, 2010 7:02 AM

All replies

  • you can query INFORMATION_SCHEMA

    select    *
    from    INFORMATION_SCHEMA.TABLES
    where    TABLE_TYPE    = 'BASE TABLE'
    
    select    *
    from    INFORMATION_SCHEMA.COLUMNS


    KH Tan
    Monday, March 29, 2010 7:02 AM
  • If you need procedures for this , you can use the below ones

    sp_tables

     

    sp_columns

     

    <Tablename>

    Monday, March 29, 2010 7:07 AM
  • EXEC

     

    sp_MSForEachTable 'SELECT ''?'', * FROM ?'

    Thanks

    Ramesh.M

    Monday, March 29, 2010 7:09 AM
  • for the amount of columns in table i add a bit from KH Tan's query

     

    select    TABLE_NAME, COUNT(column_name) NumberofColumn
    from    INFORMATION_SCHEMA.COLUMNS
    group by TABLE_NAME

     


    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

    • Proposed as answer by divince66 Wednesday, March 16, 2011 2:03 PM
    Monday, March 29, 2010 8:34 AM
  • for the amount of columns in table i add a bit from KH Tan's query

     

    select    TABLE_NAME, COUNT(column_name) NumberofColumn
    from    INFORMATION_SCHEMA.COLUMNS
    group by TABLE_NAME

     


    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

    Monday, March 29, 2010 8:34 AM