locked
SQL server Query to sort the Stored procedure as text RRS feed

  • Question

  • Hi

    I have a requirement as below :

    Stored procedure in SQL server has some 1000 lines of code where the SP uses select, insert , update ,delete and Joins on direct tables and as well as temp tables , the requirement is to get all text code from sp which is used for either select/insert except update and delete.

     

    for example :

    Create PROCEDURE testproc

    AS

    Begin

    /*Statement 1*/

    select id from emptable

    /*Statement 2*/

    select address into #empAddress from empAddresstbl

    /*Statement 3*/

    update a

    set id = 0 from usertbl a 

    /*Statement 4*/

    Update u

    set access=0 from usertbl u

    inner join permissionstbl p

    on u.userid = p.userid

    /*Statement 5*/

    select name into #empname

    from empnametbl

    inner join Nametbl on

    empname.id = name.id

     

    END

    for the given sample sp the output of query shld be

    from emptable

    from empaddresstbl

    from empnametbl

    join nametbl

     

    it shld not consider the table name from update/delete and the tables used for joins in updates/delete.

    This has to be achieved using  SQL

     Thanks,

     

    Friday, July 25, 2014 11:17 AM