«
»


TSQL Count Number Of Stored Procedures, Views, Tables or Functions

by Jon Hibbins on 4 September 2008
Filed under: Code, Database, MSSQL2005, MSSQL2008, Software Development, SQL Server, TSQL

You can use TSQL to Count Number Of Stored Procedures, Views, Tables or Functions in a Database by using the Database INFORMATION_SCHEMA view

/* Count Number Of Tables In A Database */
    SELECT COUNT(*) AS TABLE_COUNT FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE='BASE TABLE'
    /* Count Number Of Views In A Database */
    SELECT COUNT(*) AS VIEW_COUNT FROM INFORMATION_SCHEMA.VIEWS
    /* Count Number Of Stored Procedures In A Database */
    SELECT COUNT(*) AS PROCEDURE_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
    /* Count Number Of Functions In A Database */
    SELECT COUNT(*) AS FUNCTION_COUNT FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

The same methodology can be used to query for information :

/* Select Table Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
    /* Select View Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.VIEWS
    /* Select Stored Procedure Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'
    /* Select Function Information For A Database */
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

20 Responses to “TSQL Count Number Of Stored Procedures, Views, Tables or Functions”

  1. Kevin Youde Says:
    March 10th, 2009 at 4:23 pm

    Brilliant ,just what I needed to know.

  2. Sanjeet Kunwar Says:
    July 31st, 2009 at 5:50 am

    Excellent

  3. rajesh singh Says:
    August 27th, 2009 at 10:10 am

    really nice work,

    rajesh singh

  4. Rohan Mehta Says:
    October 27th, 2009 at 10:51 am

    Great Job

  5. Sai Says:
    November 17th, 2009 at 6:00 pm

    Nice and Thanks. Just what I wanted.

  6. Pavan Says:
    January 14th, 2010 at 9:22 am

    thnx man…….

  7. D.Mahesh Says:
    January 17th, 2010 at 2:53 pm

    Great ,
    Thanks very Much

  8. Yusuf Says:
    March 6th, 2010 at 4:47 pm

    It is indeed helpful!!!!

  9. Raju Says:
    May 6th, 2010 at 5:26 am

    Very Nice…

  10. leen Says:
    June 15th, 2010 at 1:02 pm

    awesome !!!!!!!!!!!!!

  11. O.ver Says:
    August 6th, 2010 at 2:02 pm

    This is helpful. Thanks!

  12. Hiren Says:
    January 13th, 2011 at 5:00 am

    Thanks dude

  13. John Says:
    February 14th, 2011 at 10:24 am

    Very helpful.
    Thanks Mate.

  14. Divya Sooraj Says:
    April 5th, 2011 at 2:18 pm

    Brilliant Work……..Very helpful….Thanks alot………!!

  15. Jabran Says:
    April 8th, 2011 at 9:23 pm

    Count of Procedures and Functions across all databases on the server

    Select name as databaseName, NULL as SPCount
    into #x
    from sys.databases where database_id > 4
    –drop table #x

    Declare @databaseName varchar(30), @Query nvarchar(max)

    While exists (select 1 from #x where Spcount is NULL)
    Begin
    Select top 1 @databaseName = databaseName from #x where SPCount is NULL
    Set @Query =
    ‘
    Update #x
    Set Spcount = (SELECT count(*) FROM ‘ + @databaseName + ‘.INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE in(”PROCEDURE”, ”FUNCTION”))
    WHERE databaseName = ”’ + @databaseName + ””

    Print @Query
    exec sp_executesql @Query
    End

    Select * from #x

  16. Niranjan Says:
    May 6th, 2011 at 12:40 pm

    Good Job!!!

  17. Karnatakapu Mallikarjun Says:
    June 21st, 2011 at 7:18 am

    Good one.

  18. Sun Says:
    August 5th, 2011 at 8:45 am

    Very useful Queries..

  19. Curtis Says:
    September 2nd, 2011 at 4:25 pm

    Thank you..very helpful

  20. shanmu Says:
    October 25th, 2011 at 1:21 pm

    the most wanted statements in sql query……

Leave a Reply