Now there are two things needs to be observed here..
As you all know we can't alter any MS Shipped sps, so we will have to create a new SP separately inside our environment. I have named it
usp_helptext because it functions very similar to
sp_helptext./*************************************************************
** File: [usp_helptext]
** Author: Prasad Sahoo
** Description: Modified version of sp_helptext(MS Shipped SP).
** Purpose: script object as drop and create to functionality
** Date: 05/24/2013
**************************************************************
** Change History
**************************************************************
** PR Date Author Change Description
** -- -------- ------- -----------------------------
** 1 05/31/2013 Prasad Sahoo Made [] independent for objects
** 2 06/06/2013 Prasad Sahoo Schema prefetch for objects
**************************************************************/
ALTER PROCEDURE usp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
AS
SET NOCOUNT ON
DECLARE @dbname sysname
,@objid INT
,@BlankSpaceAdded INT
,@BasePos INT
,@CurrentPos INT
,@TextLength INT
,@LineId INT
,@AddOnLen INT
,@LFCR INT --lengths of line feed carriage return
,@DefinedLength INT
,@ObName nvarchar(700)
,@ObType nvarchar(50)
,@ObSchemaName nvarchar(20)
,@ObNameForSchema nvarchar(700)
,@NewObjName nvarchar(1000)
,@SyscomText nvarchar(4000)
,@Line nvarchar(255)
IF LEFT(@objname, 1) != '['
BEGIN
SET @obname = '['+@objname+']'
SET @ObNameForSchema = @objname
END
ELSE
BEGIN
SET @obname = @objname
SET @ObNameForSchema = SUBSTRING(@objname, 2, LEN(@objname) - 2)
END
SELECT @ObType = type_desc FROM sys.objects WHERE object_id = object_id(@obname)
SELECT @ObSchemaName = '[' + OBJECT_SCHEMA_NAME((SELECT object_id FROM sys.objects WHERE name = @ObNameForSchema)) + ']'
SELECT @DefinedLength = 255
SELECT @BlankSpaceAdded = 0
/*Keeps track of blank spaces at end of lines. Note Len function ignores
trailing blank spaces*/
CREATE TABLE #CommentText
(LineId INT
,Text nvarchar(255))
/*
** Make sure the @objname is local to the current database.
*/
SELECT @dbname = PARSENAME(@objname,3)
IF @dbname IS NULL
SELECT @dbname = DB_NAME()
ELSE IF @dbname <> DB_NAME()
BEGIN
RAISERROR(15250,-1,-1)
RETURN (1)
END
/*
** See if @objname exists.
*/
SELECT @objid = OBJECT_ID(@objname)
IF (@objid is null)
BEGIN
RAISERROR(15009,-1,-1,@objname,@dbname)
RETURN (1)
END
-- If second parameter was given.
IF ( @columnname is not null)
BEGIN
-- Check if it is a table
IF (SELECT count(*) FROM sys.objects WHERE object_id = @objid and TYPE in ('S ','U ','TF'))=0
BEGIN
RAISERROR(15218,-1,-1,@objname)
RETURN(1)
END
-- check if it is a correct column name
IF ((SELECT 'count'=count(*) FROM sys.columns WHERE name = @columnname and object_id = @objid) =0)
BEGIN
RAISERROR(15645,-1,-1,@columnname)
RETURN(1)
END
IF (ColumnProperty(@objid, @columnname, 'IsComputed') = 0)
BEGIN
RAISERROR(15646,-1,-1,@columnname)
RETURN(1)
END
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = @objid AND encrypted = 0 AND number =
(SELECT column_id FROM sys.columns WHERE name = @columnname and object_id = @objid)
ORDER BY number,colid
FOR READ ONLY
END
ELSE IF @objid < 0 -- Handle system-objects
BEGIN
-- Check count of rows with text data
IF (SELECT count(*) from master.sys.syscomments WHERE id = @objid and text is not null) = 0
BEGIN
RAISERROR(15197,-1,-1,@objname)
RETURN (1)
END
DECLARE ms_crs_syscom CURSOR LOCAL FOR SELECT text FROM master.sys.syscomments WHERE id = @objid
ORDER BY number, colid FOR READ ONLY
END
ELSE
BEGIN
/*
** Find out how many lines of text are coming back,
** and return if there are none.
*/
IF (SELECT count(*) FROM syscomments c, sysobjects o WHERE o.xtype not in ('S', 'U')
and o.id = c.id and o.id = @objid) = 0
BEGIN
RAISERROR(15197,-1,-1,@objname)
RETURN (1)
END
IF (SELECT count(*) FROM syscomments WHERE id = @objid and encrypted = 0) = 0
BEGIN
RAISERROR(15471,-1,-1,@objname)
RETURN (0)
END
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT text FROM syscomments WHERE id = @objid and encrypted = 0
ORDER BY number, colid
FOR READ ONLY
END
/*
** else get the text.
*/
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT from ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
--Looking for end of line followed by carriage return
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)
--If carriage return found
IF @CurrentPos != 0
BEGIN
/*If new value for @Lines length will be > then the
**set length then insert current contents of @line
**and proceed.
*/
WHILE (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
--else carriage return not found
BEGIN
IF @BasePos <= @TextLength
BEGIN
/*If new value for @Lines length will be > then the
**defined length
*/
WHILE (ISNULL(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength
begin
SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))
SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')
IF LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT from ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
BEGIN
INSERT #CommentText VALUES( @LineId, @Line )
END
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
IF (SELECT type FROM sys.objects WHERE object_id = OBJECT_ID(@objname)) IN (N'P', N'PC')
BEGIN
PRINT @obname + ' = ' + @ObType
INSERT INTO #CommentText (LineId, Text) VALUES
(NULL, 'USE [' + @dbname +']')
,(NULL, 'GO')
,(NULL, '')
,(NULL, '/****** Object: StoredProcedure ' + @ObSchemaName + '.' + @obname +' Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
,(NULL, 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+ @ObSchemaName +'.'+ @obname +''') AND type in (N''P'', N''PC''))')
,(NULL, 'DROP PROCEDURE ' + @ObSchemaName + '.' + @obname)
,(NULL, 'GO')
,(NULL, '')
,(NULL, 'USE [' + @dbname +']')
,(NULL, 'GO')
,(NULL, '')
,(NULL, '/****** Object: StoredProcedure ' + @ObSchemaName + '.' + @obname +' Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
,(NULL, 'SET ANSI_NULLS ON')
,(NULL, 'GO')
,(NULL, '')
,(NULL, 'SET QUOTED_IDENTIFIER ON')
,(NULL, 'GO')
,(NULL, '')
END
ELSE IF (SELECT type FROM sys.objects WHERE object_id = OBJECT_ID(@obname)) IN (N'FN', N'IF', N'TF', N'FS', N'FT')
BEGIN
PRINT @obname + ' = ' + @ObType
INSERT INTO #CommentText VALUES
(NULL, 'USE [' + @dbname +']')
,(NULL, 'GO')
,(NULL, '')
,(NULL, '/****** Object: UserDefinedFunction ' + @ObSchemaName + '.' + @obname +' Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
,(NULL, 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+ @ObSchemaName +'.'+ @obname +''') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT''))')
,(NULL, 'DROP FUNCTION '+ @ObSchemaName + '.' + @obname)
,(NULL, 'GO')
,(NULL, '')
,(NULL, 'USE [' + @dbname +']')
,(NULL, 'GO')
,(NULL, '')
,(NULL, '/****** Object: UserDefinedFunction ' + @ObSchemaName + '.' + @obname +' Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
,(NULL, 'SET ANSI_NULLS ON')
,(NULL, 'GO')
,(NULL, '')
,(NULL, 'SET QUOTED_IDENTIFIER ON')
,(NULL, 'GO')
,(NULL, '')
END
ELSE IF (SELECT type FROM sys.objects WHERE object_id = OBJECT_ID(@obname)) IN (N'V')
BEGIN
PRINT @obname + ' = ' + @ObType
INSERT INTO #CommentText VALUES
(NULL, 'USE [' + @dbname +']')
,(NULL, 'GO')
,(NULL, '')
,(NULL, 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+ @ObSchemaName +'.'+ @obname +'''))')
,(NULL, '/****** Object: UserDefinedFunction ' + @ObSchemaName + '.' + @obname +' Script Date: '+ CONVERT(varchar(50), getdate(), 101) + ' ' + CONVERT(VARCHAR(12), GETDATE(), 114) +' ******/')
,(NULL, 'DROP '+ @ObType+ ' ' + @ObSchemaName + '.' + @obname)
,(NULL, 'GO')
,(NULL, '')
,(NULL, 'SET ANSI_NULLS ON')
,(NULL, 'GO')
,(NULL, '')
,(NULL, 'SET QUOTED_IDENTIFIER ON')
,(NULL, 'GO')
,(NULL, '')
END
ELSE
BEGIN
PRINT 'This object has either no text or incorrect name. ' + @ObType + ' ' + @obname
END
SELECT Text from #CommentText order by LineId
DROP TABLE #CommentText
return (0) -- usp_helptext
Screen capture: usp_helptext
We are done with our first requirement.. Lets move to the next..
Requirement (2): Get multiple object's DROP AND CREATE to in a single batch.
To achieve this we need two things.
- One function, which would give us CSV values into a table format. [udf_CSVSplit]
- One more stored procedure, which would execute sp_helptext in a cursor for each object in that function table. [usp_helptexts]
Now lets create above objects one by one..
udf_CSVSplit:
/*
[Author]:Prasad Sahoo
[Date of Creation]: 05/25/2013
[Purpose]: Split the String by comma separator and insert into a table.
*/
CREATE FUNCTION [dbo].[udf_CSVSplit]
(
@List varchar(max), @Delimiter varchar(10)
)
RETURNS @Ids TABLE
(Id VARCHAR(MAX)) AS
BEGIN
DECLARE @x xml
SELECT @x = CONVERT(xml,'<root><s>' + REPLACE(@List,@Delimiter,'</s><s>') + '</s></root>')
INSERT INTO @Ids(ID)
SELECT [ID] = T.c.value('.','varchar(50)')
FROM @X.nodes('/root/s') T(c)
RETURN
END Note: We don't have to call this function anywhere explicitly, this is being called implicitly from usp_helptexts.
usp_helptexts:
/*************************************************************
** File: [usp_helptexts]
** Author: Prasad Sahoo
** Description: Child script of usp_helptext
** Purpose: Merged script for multiple input objects
** Date: 06/07/2013
**************************************************************
** Change History
**************************************************************
** PR Date Author Change Description
** -- -------- ------- -----------------------------
** 1 06/07/2013 Prasad Sahoo Created
**************************************************************/
CREATE PROC usp_helptexts
(
@ObjNames nvarchar(max)
)
as
begin
SET NOCOUNT ON
DECLARE @dbname varchar(50)
set @dbname = 'USE [' + (SELECT DB_NAME()) + ']'
CREATE TABLE #MergedScript
(Texts varchar(max))
DECLARE @CurObj varchar(max)
DECLARE @CurSelect CURSOR
SET @CurSelect = CURSOR FOR
SELECT RTRIM(LTRIM(ID)) FROM (SELECT ID FROM udf_CSVSplit(@ObjNames, ',')) Temp
OPEN @CurSelect
FETCH NEXT
FROM @CurSelect INTO @CurObj
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #MergedScript (Texts)
EXECUTE Usp_helptext @CurObj
INSERT INTO #MergedScript (Texts)
VALUES (''), ('')
FETCH NEXT
FROM @CurSelect INTO @CurObj
END
CLOSE @CurSelect
DEALLOCATE @CurSelect
UPDATE #MergedScript SET Texts = '' where Texts = @dbname
SELECT Texts FROM #MergedScript
DROP TABLE #MergedScript
endI have named this sp as
usp_helptexts because it's taking multiple objects as parameter.
We are done now! Inside above stored procedure, a cursor has been declared and being used to execute usp_helptext stored proc for multiple object names from udf_CSVSplit function table.
In this way we can generate any object(SP, Fun & Views) scripts and execute them easily on any of our environments. There are many tools in the market which will give you object scripts in a single batch for hassle free deployment but this is something cool and we don't need any 3rd party help.
I did this to help my self and shared here to help community. Remember, Sharing is caring..