Cari Blog Ini

06 Juli 2012

How To Check All Stored Procedure Name and Description That Create By User

-------------------------------------------------------------------
-----code:start
-------------------------------------------------------------------
DECLARE
    @RoutineName varchar(25)
    ,@RoutineDefinition varchar(5000)

DECLARE USER_Routine CURSOR FOR
(
    select
        'Routine Name : ' + routine_name
        + ' '
        + routine_definition
    from information_schema.routines
    where left(routine_name,3) not like 'dt_'
)



OPEN USER_Routine
FETCH NEXT FROM USER_Routine

WHILE @@FETCH_STATUS=0
    BEGIN
        FETCH NEXT FROM USER_Routine
    END

CLOSE USER_Routine
DEALLOCATE USER_Routine
-------------------------------------------------------------------
-----code:end
-------------------------------------------------------------------


Example Result Messages:
-----------------------------------------------------
Routine Name : sp_requestcuti_saldocuti_hrd

-----------------------------------------------

create procedure sp_requestcuti_saldocuti_hrd
as

if EXISTS
(select * from INFORMATION_SCHEMA.tables
where table_name = '_Temp_SaldoCuti')
drop table _Temp_SaldoCuti

(1 row(s) affected)