Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34

    Unanswered: Query to get details of DB objects..

    Hi Folks
    Please assist me to make query to get the below resultset.

    1) List of all the stored procedures in a database by name, with the number of lines of code for each Procedure.
    For example;

    sp_name line_of_code
    ______________ ___________
    get_invoice_details 510
    rpt_revenue_details 710
    get_issuer_name 375

    2) List of all table along with total number of columns.
    For Example:

    Table_name number_of_coulmns
    ____________ _________________
    tbl_invoice 16
    issuer_info 13
    product_master 19


    Thanks in advance

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    1) Not 100% accurate but try
    Code:
    SELECT sp_name=object_name(id), newline_chars=sum(datalength(text)-datalength(str_replace(text,CHAR(10),NULL))+1)
    FROM syscomments
    WHERE 
    GROUP BY object_name(id)
    2)
    Code:
    SELECT o.name, colcount=count(*)
    FROM sysobjects o
    JOIN syscolumns c
      ON c.id=o.id
     AND o.type='U'
    GROUP BY o.name

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •