da quando ho iniziato a scrivere stored per SQL ho avut il problema di tenere da qualche parte che non fosse SQL le stored che scrivevo.
così mi son messo di buona lena ed approfittando di una sera con la signora in ferie (sì lo sò c’è altro da fare ma tant’è.. ) ho scritto un pò di righe
ed eccole qua, quando imparerò a copincollare per bene il codice dentro sto blocco appunti che è il mio blog sarà anche un pò più leggibile..
Max
PROCEDURE [dbo].[Uds_Save_Stored_Procedure]
AS
DECLARE @StrSQL NVarChar(400)
DECLARE @Db NVarChar(400)
DECLARE @Sp NVarChar(400)
DECLARE @i int
DECLARE @I_SP Int
DECLARE @cmd NVarChar(1000)
DECLARE @Path NVarChar(1000)
Set @path ='c:\trace\'
BEGIN
SET NOCOUNT ON;
SET @StrSQL = ' IF OBJECT_ID(''tempdb.dbo.##DB_List'') IS NOT NULL' + CHAR(13)
+ ' DROP TABLE ##DB_List ' + CHAR(13) + CHAR(13)
+ ' CREATE TABLE ##DB_List (' + CHAR(13)
+ ' id int identity(1,1)' + CHAR(13)
+ ' ,DB nvarchar(max)' + CHAR(13)
+ ' )' + CHAR(13) + CHAR(13)
+ ' Insert Into ##DB_List (DB)' + CHAR(13)
+ ' SELECT name ' + CHAR(13)
+ ' FROM master..sysdatabases' + CHAR(13)
+ ' where name not in (''master'',''tempdb'',''model'',''msdb'')' + CHAR(13)
--PRINT @strsql
EXEC sp_executesql @strsql
SET @StrSQL = ' IF OBJECT_ID(''tempdb.dbo.##SP_List'') IS NOT NULL' + CHAR(13)
+ ' DROP TABLE ##SP_List ' + CHAR(13) + CHAR(13)
+ ' CREATE TABLE ##SP_List (' + CHAR(13)
+ ' id int identity(1,1)' + CHAR(13)
+ ' ,DB nvarchar(max)' + CHAR(13)
+ ' ,SP nvarchar(max)' + CHAR(13)
+ ' )' + CHAR(13) + CHAR(13)
--PRINT @strsql
EXEC sp_executesql @strsql
SET @i = 0
select top 1 @i = id
from ##DB_List
where id > @i
order by id
While @@Rowcount = 1
BEGIN
select @db = DB
from ##DB_List
where id = @i
SET @StrSQL = ' USE [' + @Db + ']' + CHAR(13) + CHAR(13)
+' Insert into ##SP_List (DB,SP)' + Char(13)
+' SELECT ''' + @db +''' ,name' + Char(13)
+' FROM sys.objects' + Char(13)
+' WHERE type = ''P''' + Char(13)
+' AND left(name,3) ''sp_''' + Char(13)
+' AND left(name,3) ''dt_''' + Char(13)
+' AND left(name,7) ''aspnet_''' + Char(13)
EXEC sp_executesql @strsql
--PRINT @strSQL
select top 1 @i = id
from ##DB_List
where id > @i
order by id
END
SET @i = 0
select top 1 @i = id
from ##SP_List
where id > @i
order by id
While @@Rowcount = 1
BEGIN
select @db = DB, @sp = SP
from ##SP_List
where id = @i
SET @strSQL = ' Use [' + @Db + ']' + char(13) + char(13)
+ ' IF OBJECT_ID(''tempdb.dbo.##sp_helptext'') IS NOT NULL' + char(13)
+ ' drop table ##sp_helptext ' + char(13)
+ ' create table ##sp_helpText (' + char(13)
+ ' id int identity(1,1)' + char(13)
+ ' ,tmptext nvarchar(max))' + char(13) + char(13)
+ ' insert into ##sp_helptext exec sp_helptext ['+ @Sp +']'+ char(13)
--PRINT @strsql
EXEC sp_executesql @strsql
set @i_sp = 0
select top 1 @i_sp = id
from ##sp_helpText
where id > @i_sp
order by id
while @@rowcount = 1
BEGIN
SELECT @cmd = 'echo ' + left(tmptext,LEN(tmptext)-2) + ' >> '+ @Path + '[' + @db + '].dbo.[' + @Sp + '].sql'
from ##sp_helpText
where id = @i_sp
--PRINT @cmd
exec master..xp_cmdshell @cmd
select top 1 @i_sp = id
from ##sp_helpText
where id > @i_sp
order by id
END
select top 1 @i = id
from ##SP_List
where id > @i
order by id
END
END
