-------------------------------------------------------------------------------
-- TURN Ole Automation Procedures ON
-------------------------------------------------------------------------------
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
PRINT ''
PRINT ''
-------------------------------------------------------------------------------
-- PARAMTERS
-------------------------------------------------------------------------------
DECLARE @rootPath nvarchar(100); SET @rootPath = 'C:\MyAssembly\'
-------------------------------------------------------------------------------
-- EXTRACT ALL USER-DEFINED ASSEMBLIES
-------------------------------------------------------------------------------
DECLARE @name nvarchar(max)
DECLARE @obj varbinary(max)
DECLARE #cur CURSOR FOR
--SELECT af.name, af.content
SELECT REPLACE(
REPLACE(af.name, '\', '_')
, '/', '_')
, af.content
FROM sys.assemblies a
INNER JOIN sys.assembly_files af
ON a.assembly_id = af.assembly_id
WHERE is_user_defined <> 0
OPEN #cur
FETCH NEXT FROM #cur INTO @name, @obj
DECLARE @ObjectToken INT
DECLARE @path nvarchar(max)
WHILE @@FETCH_STATUS = 0
BEGIN
SET @path = @rootPath + @name
PRINT @name + ' --> ' + @path
-------------------------------------------------------------------------------
-- WRITE FILE TO DISK
-------------------------------------------------------------------------------
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @obj
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @path, 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken
FETCH NEXT FROM #cur INTO @name, @obj
END
CLOSE #cur
DEALLOCATE #cur
PRINT ''
PRINT ''
-------------------------------------------------------------------------------
-- TURN Ole Automation Procedures OFF
-------------------------------------------------------------------------------
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO
Testato su SQL 2005. Via.
