Como personalizar nuestras plantillas del SQL Server Management Studio: Creando una plantilla personalizada para procedimientos almacenados

JuanCastillo 25 mayo, 2018 0 comentarios

Personalizando plantillas de SSMS para procedimientos almacenados

Hola a tod@s

En esta oportunidad venimos con algo de estetica para nuestros procedimientos almacenados, aquí hay otro truco en SQL Server Management Studio. Muchos de ustedes pueden crear un Nuevo Procedimiento Almacenado utilizando, Click derecho crear nuevo procedimiento, sin embargo en cada oportunidad toca colocar datos como el autor, fecha de creacion, entre otras cosas, pero si les digo, que podemos cambiar esa plantilla, pues si es posible

Personalizar ayuda a mejorar tiempos de creación de codigo, adicionalmente permite tener claridad del contenido de nuestros procedimientos mejorando asi, la calidad de nuestro codigo

Actualmente realizan esto

Procedimiento almacenado

Codigo por defecto

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

-- Add the parameters for the stored procedure here

<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

Posterior al cambio

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Use the Specify Values for Template Parameters 
-- command *** Ctrl-Shift-M *** to fill in the parameter

-- ========================================================================================================================
-- Author: : Juan M Castillo 
-- Create date: 2018-05-25
-- Company : SQListo® 
-- Description: <Description,,>
-- ========================================================================================================================
-- DATE CHANGED BY CHANGE CODE DESCRIPTION 
-- 2018-05-25 14:12:00 Juan M Castillo [#000000000] Creacion de procedimiento 
-- ========================================================================================================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- Use the Specify Values for Template Parameters

-- command *** Ctrl-Shift-M *** to fill in the parameter

-- ========================================================================================================================

-- Author: : Juan M Castillo

-- Create date: 2018-05-25

-- Company : SQListo®

-- Description: <Description,,>

-- ========================================================================================================================

-- DATE CHANGED BY CHANGE CODE DESCRIPTION

-- 2018-05-25 14:12:00 Juan M Castillo [#000000000] Creacion de procedimiento

-- ========================================================================================================================

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

-- Add the parameters for the stored procedure here

<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,

<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>

END

Ubicación de las plantillas, por version del SQL Server

Version	Extension	Ruta de la plantilla
SQL 2005	.sql	C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\sqlfile.sql
SQL 2008 & 2008R2	.sql	C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\sqlfile.sql
SQL 2012	.sql	C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
SQL 2014	.sql	C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql
SQL 2016	.sql	C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql

En caso de no encontrar los archivos, podemos buscar el archivo con el nombre «Create Stored Procedure (New Menu).sql» mediante el buscador de archivos de windows

Otra opcion es editar mediante el explorador de plantillas