Sample Header Ad - 728x90

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