Use master
go
if objectproperty( object_id('sp_ghf') , 'IsProcedure' ) = 1 drop proc sp_ghf
go
create proc sp_ghf
@object_name varchar(128) = 'Dummy',
@object_type char(1) = 'P',
@table_name varchar(128) = NULL
as
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    11 July 1999                                            */
/*      Description:    SP for generating a template for SP, view or trigger.   */
/*                      The template also contains a comment that uses a VSS    */
/*                      keyword expansion macro to automatically update check-in*/
/*                      information. The SP name stands for sp that generates   */
/*                      the header & footer. Just a abbreviated name for less   */
/*                      typing.                                                 */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
declare @go char(2) 
select @go = 'go' 
if @object_type = 'P' 
print 
'IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsProcedure'') = 1 
BEGIN 
    DROP PROCEDURE dbo.' + @object_name + ' 
    IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsProcedure'') = 1 
        PRINT ''<<< FAILED DROPPING PROCEDURE dbo.' + @object_name + ' >>>'' 
    ELSE 
        PRINT ''<<< DROPPED PROCEDURE dbo.' + @object_name + ' >>>'' 
END 
' + @go + ' 
CREATE PROCEDURE ' + @object_name + ' 
--WITH ENCRYPTION 
AS 
/********************************************************************************* 
 * $History: $ 
 *********************************************************************************/ 
' + @go + ' 
IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsProcedure'') = 1 
BEGIN 
     GRANT EXECUTE ON dbo.' + @object_name + ' To Public 
     PRINT ''<<< CREATED PROCEDURE dbo.' + @object_name + ' >>>'' 
END 
ELSE 
    PRINT ''<<< FAILED CREATING PROCEDURE dbo.' + @object_name + ' >>>'' 
' + @go 

if @object_type = 'V' 
print 
'IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsView'') = 1 
BEGIN 
    DROP VIEW dbo.' + @object_name + ' 
    IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsView'') = 1
        PRINT ''<<< FAILED DROPPING VIEW dbo.' + @object_name + ' >>>'' 
    ELSE 
        PRINT ''<<< DROPPED VIEW dbo.' + @object_name + ' >>>'' 
END 
' + @go + ' 
CREATE VIEW ' + @object_name + ' 
--WITH ENCRYPTION 
AS 
/********************************************************************************* 
 * $History: $ 
 *********************************************************************************/ 
IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsView'') = 1 
BEGIN 
    GRANT SELECT ON dbo.' + @object_name + ' To Public 
    PRINT ''<<< CREATED VIEW dbo.' + @object_name + ' >>>'' 
END 
ELSE 
    PRINT ''<<< FAILED CREATING VIEW dbo.' + @object_name + ' >>>'' 
' + @go 

if @object_type = 'T' 
print 
'IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsTrigger'') = 1 
BEGIN 
    DROP TRIGGER dbo.' + @object_name + ' 
    IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsTrigger'') = 1 
        PRINT ''<<< FAILED DROPPING TRIGGER dbo.' + @object_name + ' >>>'' 
    ELSE 
        PRINT ''<<< DROPPED TRIGGER dbo.' + @object_name + ' >>>'' 
END 
' + @go + ' 
/********************************************************************************* 
 * $History: $ 
 *********************************************************************************/ 
IF OBJECTPROPERTY(OBJECT_ID(''dbo.' + @object_name + '''), ''IsTrigger'') = 1 
    PRINT ''<<< CREATED TRIGGER dbo.' + @object_name + ' >>>'' 
ELSE 
    PRINT ''<<< FAILED CREATING TRIGGER dbo.' + @object_name + ' >>>'' 
' + @go 
go

This page was last updated on May 01, 2006 04:28 PM.