Скрытый текст: показать
Листинг 4.2 (продолжение)
$Workfile: sp_ggshowversion.SQL $
$Author: Khen $. Email: khen@khen.com
$Revision: 1 $
Example: sp_GGShowVersion
Created: 2000-04-03. $Modtime: 4/29/00 2:49p $.
*/
AS
DECLARE @GGVersion varchar(30), @Revision varchar(30), @author varchar(30),
@Date varchar(30), @Modtime varchar(30)
SELECT @GGVersion='GGVersion: ',@Revision='$'+'Revision: ',@Date='$'+'Date: ',
@Modtime='$'+'Modtime: ',@Author='$'+'Author: '
SELECT DISTINCT Object=SUBSTRING(o.name,1,30),
Type=CASE o.Type
WHEN 'P' THEN 'Procedure'
WHEN 'V' THEN 'View'
WHEN 'TR' THEN 'Trigger'
WHEN 'FN' THEN 'Function'
ELSE o.Type
END,
Version=CASE
WHEN CHARINDEX(@GGVersion,c.text)<>0 THEN
SUBSTRING(LTRIM(SUBSTRING(c.text,CHARINDEX(@GGVersion,c.text)+LEN(@GGVersio
n),10)),1,ISNULL(NULLIF(CHARINDEX(CHAR(13),LTRIM(SUBSTRING(c.text,CHARINDEX
(@GGVersion,c.text)+LEN(@GGVersion),10)))-1,-1),1))
ELSE NULL
END,
Revision=CONVERT(int,
CASE
WHEN CHARINDEX(@Revision,c.text)<>0 THEN
SUBSTRING(LTRIM(SUBSTRING(c.text,CHARINDEX(@Revision,c.text)+LEN(@Revision)
,10))
,1,ISNULL(NULLIF(CHARINDEX('
',LTRIM(SUBSTRING(c.text,CHARINDEX(@Revision,c.text)+LEN(@Revision),10)))-
1,-1),1))
ELSE '0'
END),
Created=o.crdate,
Owner=SUBSTRING(USER_NAME(uid),1,10),
'Last Modified By'=
SUBSTRING(LTRIM(SUBSTRING(c.text,CHARINDEX(@Author,c.text)+LEN(@Author),10)
),1,ISNULL(NULLIF(CHARINDEX('
$',LTRIM(SUBSTRING(c.text,CHARINDEX(@Author,c.text)+LEN(@Author),10)))-1,-
1),1)),
'Last Checked In'=CASE WHEN CHARINDEX(@Date,c.text)<>0 THEN
SUBSTRING(LTRIM(SUBSTRING(c.text,CHARINDEX(@Date,c.text)+LEN(@Date),15)),1,
ISNULL(NULLIF(CHARINDEX('
$',LTRIM(SUBSTRING(c.text,CHARINDEX(@Date,c.text)+LEN(@Date),20)))-1,-
1),1)) ELSE NULL END,
'Last
Modified'=SUBSTRING(LTRIM(SUBSTRING(c.text,CHARINDEX(@Modtime,c.text)+LEN(@
Modtime),20)),1,ISNULL(NULLIF(CHARINDEX('
$',LTRIM(SUBSTRING(c.text,CHARINDEX(@Modtime,c.text)+LEN(@Modtime),20)))-
1,-1),1))
FROM dbo.syscomments c RIGHT OUTER JOIN dbo.sysobjects o ON c.id=o.id
WHERE o.name LIKE @Mask
AND (o.type LIKE @ObjType AND o.TYPE in ('P','V','FN','TR'))
AND (c.text LIKE '%'+@Revision+'%' OR c.text IS NULL)
AND (c.colid=(SELECT MIN(c1.colid) FROM syscomments c1 WHERE c1.id=c.id) OR c.text IS NULL)
ORDER BY Object
GO
GRANT ALL ON dbo.sp_GGShowversion TO public
GO
EXEC dbo.sp_GGShowVersion
(Результаты сокращены)
Object Type Version Revision Created
------------------------ --------- -------- -------- ---------------------
sp_created Procedure NULL 2 2000-04-08 00:19:51.680
sp_GGShowVersion Procedure 2.0.1 1 2000-04-29 15:30:56.197
sp_hexstring Procedure NULL 1 2000-04-08 15:12:21.610
sp_object_script_commentsProcedure NULL 1 2000-04-29 12:59:08.250
sp_usage Procedure NULL 6 2000-04-07
20:37:54.930
позвони мне на городской!
