Tuesday 20 August 2019

SQL Server : Query for Scripting out multiple procs

use Myproctest_DB
go
Begin
Set nocount on
/***********************************************/
/*Mention proc names In first Select Statement*/
/*Mention folder Path where you want to place the proc scripts in the variable @DirPath = 'e:\temp\ProcMove\Original'*/

/*Possible future enhancements */
/***************************************/
/* 1:- Quoted Identifier on */ -- Done (20 Aug 2019)
/* 2:- Replacement Strings */
/* 3:- Object Permissions */ --  Done (20 Aug 2019)
/* Version 2 - 20 aug 2019 */
/************************************************/

--Cleaning up temp tables
If (Select OBJECT_ID('tempdb..#dir')) is not null Drop table #dir
If (Select OBJECT_ID('tempdb..##ProcDef')) is not null GOTO CleanUp
Print 'Table already exists Pleae double check if someone is using it'--drop table ##ProcDef
If (Select OBJECT_ID('tempdb..#TmpProcDef')) is not null drop table #TmpProcDef
If (Select OBJECT_ID('tempdb..#tmp2')) is not null drop table #tmp2

select state_desc+' '+b.permission_name+' on ['+d.name+'].'+c.name+' to ['+a.name+']' COLLATE DATABASE_DEFAULT
from sys.database_principals a join
sys.database_permissions b on a.principal_id=b.grantee_principal_id join
sys.objects c on b.major_id=c.object_id join
sys.schemas d on c.schema_id=d.schema_id



Declare @DirPath varchar(2000), @DirCmd Varchar(2000)
Set @DirPath = 'e:\temp\ProcMove\Original'

Create table #tmp2 (sno int identity(1,1),srvName sysname, dbName sysname ,xtype sysname, Pname sysname)

Insert into #tmp2 (srvName,dbName,xtype,Pname)
Select @@Servername,db_name(),type_desc,Quotename(OBJECT_SCHEMA_NAME(object_id),'[') + '.' + QUOTENAME(so.name,'[') as Pname from sys.objects so
where so.name in ('phil_response_summary_report')

Select * from #tmp2



-- Checking and Creating the Directory
Set @DirCmd = 'Dir ' + @DirPath

create table #dir (outputs varchar(4000))
insert into #dir
Exec master..xp_cmdshell @DirCmd

IF EXISTS (SELECT * FROM #dir WHERE outputs LIKE '%' + @DirPath + '%')
Begin
Print 'Directory Already Exists'
End
Else
Begin
Print 'Directory Does not exists creating ' + @DirPath
Select @DirCmd = 'MD ' + @DirPath
Exec Master..xp_cmdshell @DirCmd
End
-- Checking and Creating the Directory Ends Here--------------------------



Create table ##ProcDef
(
PSno int identity(1,1),
Pcode Varchar(4000)
)


Create table #TmpProcDef
(
PSno int identity(1,1),
Pcode Varchar(4000)
)


Truncate table ##ProcDef

Declare @sno int, @i int
Select @sno= max(sno) from #tmp2
--Select @sno = 2
--Print @sno
Set @i = 1

While (@i<= @sno )
Begin

Truncate table ##ProcDef
Truncate table #TmpProcDef
Declare @srvName sysname, @dbName sysname, @Xtype sysname, @Pname sysname
Declare @drop_def varchar(50)
Declare @lbrk Char(5), @Tab char(5)
Select @lbrk = Char(13) + Char(10)
Select @tab = Char(9)
--Select ascii(' ')
--Select top 10 * from #tmp2

Select  @srvName = srvName,@dbName =dbName, @xtype = Xtype, @Pname = pname from #tmp2 where sno = @i
If  (@xtype = 'SQL_STORED_PROCEDURE')
  Select @drop_def = 'Drop Procedure '
IF @xtype = 'VIEW'
Select @drop_def = 'Drop View '
IF (@xtype = 'SQL_SCALAR_FUNCTION' OR @xtype = 'SQL_TABLE_VALUED_FUNCTION' OR @xtype = 'SQL_INLINE_TABLE_VALUED_FUNCTION')
Select @drop_def = 'Drop Function '


Declare @drop_proc1 varchar(1000),@drop_proc2 varchar(1000),@drop_proc3 varchar(1000),@drop_proc4 varchar(1000),@drop_proc5 varchar(1000)
Declare @drop_proc6 varchar(1000), @drop_Proc7 Varchar(1000)
Declare @verify_proc1 varchar(1000),@verify_proc2 varchar(1000),@verify_proc3 varchar(1000),@verify_proc4 varchar(1000),@verify_proc5 varchar(1000)
Declare @verify_proc6 varchar(1000),@verify_proc7 varchar(1000),@verify_proc8 varchar(1000),@verify_proc9 varchar(1000)
Select @drop_proc1 = 'IF OBJECT_ID(N'''+@Pname+''') IS NOT NULL'
Select @drop_proc2 = @tab + 'BEGIN ' + @lbrk + @tab + @drop_def + @Pname
Select @drop_proc3 = @tab + 'IF OBJECT_ID(N'''+ @Pname +''') IS NOT NULL'
Select @drop_proc4 = @tab + 'PRINT N''<<< ****FAILED DROPPING PROCEDURE '+ @Pname +' >>>'''
Select @drop_Proc5 = @tab + ' ELSE '
Select @drop_Proc6 = @tab + ' PRINT N''<<< DROPPED PROCEDURE '+ @Pname + ' >>>'' ' + @lbrk +'END'+@lbrk + 'Go'
--Select @drop_Proc7 = '/*************************************************/'



Insert into ##ProcDef Values (@drop_proc1)
Insert into ##ProcDef Values (@drop_proc2)
Insert into ##ProcDef Values (@drop_proc3)
Insert into ##ProcDef Values (@drop_proc4)
Insert into ##ProcDef Values (@drop_proc5)
Insert into ##ProcDef Values (@drop_proc6)
--Insert into ##ProcDef Values (@drop_proc7)
Insert into ##ProcDef Values ('   ')

If  (SELECT uses_quoted_identifier FROM sys.sql_modules  WHERE object_id =object_id(@Pname)) = 1
Begin
Select @drop_Proc7 = 'SET QUOTED_IDENTIFIER ON '+@lbrk + 'GO ' + @lbrk
Print @drop_Proc7
Insert into ##ProcDef Values (@drop_proc7)
Insert into ##ProcDef Values ('   ')
End


--Print @drop_proc1 + @drop_proc2 + @drop_proc3 + @drop_proc4 + @drop_proc5 + @drop_proc6 + @drop_proc7

--Get Proc_definition
Declare @get_ProcDef varchar(1000)
SET @get_procdef = (SELECT '['+@srvName +'].'+ @dbName +'.dbo.sp_helptext_spade '''+ @Pname + '''')
--Print @get_procdef


Truncate table #TmpProcDef
INSERT INTO #TmpProcDef
EXECUTE (@get_procdef)


--please do not modify this statement, this formatting has a special purpose
update #TmpProcDef   
set Pcode = replace(Pcode,'
','')
--set Pcode = replace(Pcode,@lbrk,'')


Insert into ##ProcDef select Pcode from #TmpProcDef order by PSno
insert into ##ProcDef values ('               ')
insert into ##ProcDef values ('GO')
insert into ##ProcDef values ('/*******Proc Ends Here******************/')
insert into ##ProcDef values ('               ')


--- this will help in verifying the Proc

Select @verify_proc1 = 'IF OBJECT_ID(N''' + @pname + ''') IS NOT NULL'
Select @verify_proc2 = @tab + 'PRINT N''<<< CREATED OBJECT '  + @pname +' >>>'''
Select @verify_proc3 = @tab + 'ELSE'
Select @verify_proc4 = @tab + 'PRINT N''<<< **** FAILED CREATING OBJECT '  + @pname + ' >>>'''
Select @verify_proc5 = @Tab + 'GO'
Select @verify_proc6 = @Tab + ' '
--Select @verify_proc7 =
--Select @verify_proc8 =

Insert into ##ProcDef values(@verify_proc1)
Insert into ##ProcDef values(@verify_proc2)
Insert into ##ProcDef values(@verify_proc3)
Insert into ##ProcDef values(@verify_proc4)
Insert into ##ProcDef values(@verify_proc5)
Insert into ##ProcDef values(@verify_proc6)

--Select * from ##ProcDef
--scripting proc into text file
Declare @set_ProcDeftxt varchar(1000)
--SET @set_procdeftxt = ('sqlcmd -S '+@@servername+' -E -q" SET NOCOUNT ON SELECT ltrim(rtrim(Pcode)) FROM rpscdr1_mo.dbo.##ProcDef ORDER BY PSno " -W -h -1 -o "e:\temp\ProcMove\Original\'+ @Pname +'.sql"')
SET @set_procdeftxt = ('sqlcmd -S '+@@servername+' -E -q" SET NOCOUNT ON SELECT ltrim(rtrim(Pcode)) FROM ##ProcDef ORDER BY PSno " -W -h -1 -o "'+ @dirPath +'\'+ @Pname +'.sql"')
--Print @set_procdeftxt


declare @result bit
EXEC @result = master.dbo.xp_cmdShell @set_procdeftxt
IF @result = 0
Print 'All set'
Else
Print 'Error Encountered'

--select len(Pcode) from ##ProcDef
--Select Pcode from ##ProcDef


-- This should be the last line
Select @i = @i + 1
End

cleanup:
Print 'Global ##ProcDef Table already Exist Possibly someone is using it or someone has kept his session opened'
Print 'Exiting the code'

--SELECT ltrim(rtrim(Pcode)) FROM ##ProcDef order by PSno
End

No comments:

Post a Comment