How does SMO make it easy to grab all code on a Microsoft SQL Server?
2
votes
4
answers
855
views
There has recently been [a lot of talk](https://curiousaboutdata.com/2024/08/18/t-sql-tuesday-177-roundup-managing-database-code/) about putting database code in source control. I have read widely on this topic and there appears to be an unstated assumption that using SMO through PowerShell makes it very easy to do the following simultaneously:
1. Connect to a Microsoft SQL Server.
2. Grab every unit of code in a database (e.g. every table, index, stored procedure, function, view, permission, database config, ... *but not data*)
3. Save them in separate files, so that they're useful for source control.
Despite my best efforts, I can find no off-the-shelf SMO code that does this. It really is as if there is a secret SMO script that I cannot find. To me, this suggests that such a script is so easy to write that nobody has bothered publicising it.
dbatools has a handful of specialised functions for certain types of object (e.g.
Get-DbaDbStoredProcedure
), but finding every required dbatools function for this and correctly piping it to Export-DbaScript
would be a big job. I'm also aware of some Redgate tools, **but I'm specifically asking about SMO**.
So, to put my question in an answerable form: What parts of the SMO make it very easy to write a script that simultaneously satisfies all of my three numbered points above?
Asked by J. Mini
(1237 rep)
Aug 29, 2024, 07:22 PM
Last activity: Sep 6, 2024, 05:26 AM
Last activity: Sep 6, 2024, 05:26 AM