Enter databasename, schema, T-SQL, object name, if access should be granted to public and execute. It creates a wrapper for the procedure, for example and prints if the procedure was dropped and or created.
Enter databasename, schema, T-SQL, object name, if access should be granted to public and execute. It creates a wrapper for the procedure, for example and prints if the procedure was dropped and or created.
/******************************************************************************** *Enter Variables and execute to create and Print Stored Procedure Wrapper *Try and figure out a way to script multiple SPs and have the wrapper for each one. *1.Enter Database or double check Database *2.Enter or double check Schema *3.Enter Object Name or double Check Object Name *4.Grant access to Public - this will allow all to execute the Object ********************************************************************************/DECLARE @database NVARCHAR(MAX) = N''-- Enter Database ,@schema NVARCHAR(MAX) = N''-- Enter Schema ,@SP_nameNVARCHAR(MAX) = N''-- Enter SP Name ,@SQL_codeNVARCHAR(MAX) = N''-- Enter SQL Code Here ,@objectNVARCHAR(MAX) = 'PROCEDURE'-- Enter Object type here ,@grantToPublic CHAR(3) = 'NO'-- Grant Access to Public? YES/NO ,@scriptdate SMALLDATETIME = GETDATE() ,@wrapperNVARCHAR(MAX) = N'' ,@objectname NVARCHAR(MAX); SET @objectname = ( SELECT CASE WHEN @object = 'PROCEDURE' THEN 'StoredProcedure ' WHEN @object = 'FUNCTION' THEN 'UserDefinedFunction ' ELSE 'Not Defined' END ) SET @SQL_code = ''; /******************************************************************************** *EORROR CHECK LAST LINE OF DEFENCE ********************************************************************************/IF @database = '' OR @schema = '' OR @SP_name = '' BEGIN RAISERROR('ERROR - Check variable values! @database, @schema or @SP_name are BLANK!!!', 11, 1) WITH NOWAIT RETURN END; /**************************************************************************************************/SET @database = @database + '.'; SET @schema = @schema + '.'; SET @wrapper = 'USE ' + REPLACE(@database,'.','') + '; GO /******Object:' + @objectname + @database + @schema + @SP_name + 'Script Date:' + CONVERT(CHAR(10), @scriptdate, 101) + ' ' + CONVERT(CHAR(8),CONVERT(TIME,@scriptdate)) + '******/ IF OBJECT_ID(' + CHAR(39) + @database + @schema + @SP_name + CHAR(39) + ') IS NOT NULL BEGIN DROP ' + @object + ' ' + @schema + @SP_name + ' IF OBJECT_ID(' + CHAR(39) + @database + @schema + @SP_name + CHAR(39) + ') IS NOT NULL PRINT ''<<< FAILED DROPPING ' + @object + ' ' + @database + @schema + @SP_name + ' >>>'' ELSE PRINT ''<<< DROPPED ' + @object + ' ' + @database + @schema + @SP_name + ' >>>'' END; GO SET QUOTED_IDENTIFIER ON; GO SET ANSI_NULLS ON; GO /**************************************************************************************************/ -- SQL BEGIN ' + @SQL_code + ' /************************************************************************************************** *************************************************************************************************** *************************************************************************************************** *************************************************************************************************** *************************************************************************************************** *************************************************************************************************** *************************************************************************************************** *************************************************************************************************** *************************************************************************************************** **************************************************************************************************/ GO SET NOCOUNT OFF; GO SET QUOTED_IDENTIFIER OFF; GO SET ANSI_NULLS ON; GO ' IF UPPER(@grantToPublic) = 'YES' SET @wrapper = @wrapper + ' GRANT EXECUTE ON ' + @database + @schema + @SP_name + ' to Public ' + CHAR(10) + 'GO' SET @wrapper = @wrapper + ' IF OBJECT_ID(' + CHAR(39) + @database + @schema + @SP_name + CHAR(39) + ') IS NOT NULL PRINT ''<<< CREATED ' + @object + ' ' + @database + @schema + @SP_name + ' >>>'' ELSE PRINT ''<<< FAILED TO CREATE ' + @object + ' ' + @database + @schema + @SP_name + ' >>>'' GO ' /**************************************************************************************************/ PRINT @wrapper;