For one of my database projects I needed to script out 3 databases including all objects, SSIS packages and SSRS reports.
There is no one-click solution, especially as many of the database objects are encrypted.
After scratching my head a fair bit, I came up with the following as the most efficient.
For the database objects I used the built-in “Generate Scripts” feature from within SSMS to script out the databases and all objects. By setting the option to continue after errors, I was left with a script with would create each database and all unencrypted objects.
The tricky bit was around accessing those encrypted objects. thankfully Red Gate tools came to the rescue.
I ran the following script to determine all the encrypted objects:
SELECT SS.Name + '.' + SO.Name AS ObjectName, SO.Type AS ObjectType FROM sys.SQL_Modules AS SM INNER JOIN sys.Objects AS SO ON SM.Object_Id = SO.Object_Id INNER JOIN sys.Schemas AS SS ON SO.Schema_Id = SS.Schema_Id WHERE SO.Type IN ( 'P', -- Procedures 'V', -- Views 'FN', -- Functions 'T' -- Triggers ) ORDER BY 2, 1
With this list, I used SQL Prompt to access the creation script for each object. It was a bit onerous to say the least as in total there were about 600 encrypted objects across the 3 databases, but it got the job done.
For the reports I used a great utility called RS Scripter, details at the following URL:
And for the SSIS packages I used a modified version of the following cheeky wee SSIS package, as I needed to pull them from a 2005 db:
It took a while but I got there in the end !