책보고 한번 옮겨 보았습니다.

저장프로시져가 몇백개가 넘어가니 이제는 버전관리도 필요하고

누가 만든 프로시져 인지도 알아야 하고

뭔가 체계적으로 되어야 한다고 절실히 느끼고 있었는데

책에 소개된 방법으로 해보니 정말 멋집니다.

혹시나 모르시는 분은 적용해 보십시요 사랑 받을겁니다. ^-^

use master
go
if object_id('dbo.sp_usage') is not null
drop proc dbo.sp_usage
go
create proc dbo.sp_usage
-- 필수 입력 매개변수
@objectname sysname = null,
@desc sysname = null,
-- 선택 입력 매개변수
@parameters varchar(8000) = null,
@returns varchar(8000) = null,
@example varchar(8000) = null,
@workfile sysname = null,
@author sysname = null,
@email sysname = '(none)',
@version sysname = null,
@revision sysname = '0',
@datecreated smalldatetime = null,
@datelastchanged smalldatetime = null


AS
SET NOCOUNT ON
IF (@objectname+@desc IS NULL) GOTO Help
PRINT 'Object: '+@objectname
PRINT 'Descriptrion: '+@desc

IF (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsProcedure')=1)
OR (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsExtendedProc')=1)
OR (OBJECTPROPERTY(OBJECT_ID(@objectname),'IsReplProc')=1)
OR (LOWER(LEFT(@objectname,3))) = 'sp_' BEGIN
PRINT CHAR(13) + 'Usage:'+@objectname+' '+@parameters
PRINT CHAR(13) + 'Returns: ' +@returns
END

IF (@workfile IS NOT NULL)
PRINT CHAR(13) + '$Workfile: '+@workfile+' $'
IF (@author IS NOT NULL)
PRINT CHAR(13) + '$Author: '+@author+' $. Email: '+@email
IF (@version IS NOT NULL)
PRINT CHAR(13) + '$Revisision: '+@version+'.'+@revision+' $'
IF (@example IS NOT NULL)
PRINT CHAR(13) + 'Example: ' +@example
IF (@datecreated is not null) begin
declare @datefmt varchar(8000), @dc varchar(30), @lc varchar(30)
set @dc=convert(varchar(30), @datecreated, 120)
set @lc=convert(varchar(30), @datelastchanged, 120)
print char(13) + 'Created: '+CASE
datediff(ss, convert(char(8), @datecreated,108), '00:00:00') when 0 then
left(@dc,10) else @dc end
+'. $Modtime: '+CASE
datediff(ss,convert(char(8),@datelastchanged,108),'00:00:00') when 0 then
left(@lc,10) else @lc END+ ' $. '
end

return 0

Help:
EXEC dbo.sp_usage @objectname = 'sp_usage',
@desc='Provides usage information for stored procedures and descriptions of ther typs of objects',
@parameters='@objectname=''ObjectName'',@desc=''Description of object''
[,@parameters=''param1, param2...'']
[,@example=''Example of usage'']
[,@workfile=''File name of script'']
[,@author=''Author email'']
[,@email=''Author email'']
[,@version=''Version number of info'']
[,@revision=''Revision number or info'']
[,@datecreated=''Date created'']
[,@datelastchanged=''Date last changed'']',
@example='sp_usage @objectname=''sp_who'',
@desc=''Returns a list of currently running jobs'',
@parameters=[@loginname]',
@author='김민석',
@workfile='sp_usage.sql',
@email='minsouk@hotmail.com',
@version='1', @revision='1',
@datecreated='7/3/05', @datelastchanged='7/4/05'
return -1







sp_usage

sp_usage @objectname='sp_who',
@desc='Returns a list of currently running jobs',
@parameters='sa'

sp_who sa