A few days ago, I was searching for a way to export MS SQL schema of all databases of a server for backup and migration purposes. Although I found some tools, what I needed was a way to get stored procedures, functions, views, triggers and everything even if it was encrypted.
Since I couldn’t find a way to automate this (e.g. run it every night to backup schema of my server), I thought it would be a nice weekend project.
You can read more about it, or download project from https://github.com/georgekosmidis/mssql-schema-dump!
Problem 1, automate script export:
After some google search, I found out that 4 Microsoft SqlServer DLLs expose collections of all available objects in db:
Now it was easy to get a server object and iterate every collection:
Problem 2, encrypted objects:
Again, after some google search I found that you can actually decrypt most of the info these object hold with a Dedicated Administrator Connection, also known as DAC. That means, that for sa logins only, you could enable DAC on a server, reconnect, get your encrypted objects and disable it again!
That was it! The full working code that retrieves any encrypted object (as far as I know), along with the entire solution is on github: https://github.com/georgekosmidis/mssql-schema-dump