Scripting out procs and user defined functions with PowerShell

I decided to take SonarCube for a spin to run some static code analysis. In order to do that I needed some code that I can display here without breaking laws.

I a real world, you would just point SonarCube to your code repository, but in order to show you how to run it so that you can follow along, I decided to go this route

I decided to use the AdventureWorks database to script out some procs and functions. If you want to follow what I am doing here, grab the backup from here: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

On your C drive create a folder named DB, in the DB folder create 2 folders, one name data and the other log

To restore the DB you can run this command (assuming your backup is in the c:\temp directory

USE [master]
RESTORE DATABASE [AdventureWorks2017] 
FROM  DISK = N'C:\temp\AdventureWorks2017.bak' WITH  FILE = 1,  
MOVE N'AdventureWorks2017' 
TO N'C:\DB\DATA\AdventureWorks2017.mdf',  
MOVE N'AdventureWorks2017_log' 
TO N'C:\DB\LOG\AdventureWorks2017_log.ldf',  
NOUNLOAD,  STATS = 5

 Now It's time to script out the procs and user defined functions

But first we need a place to store them

In my case I decided to create 2 folders in the c:\temp directory
StoredProcedures
UserDefinedFunctions

Now it's time to script out the procs and user defined functions, I will be using Powershell from that, you will need to install the sql server module, you can download it here

https://docs.microsoft.com/en-us/sql/powershell/download-sql-server-ps-module?view=sql-server-2017

Follow the instruction if you get any errors.

Now it's time to run the script, you will need to run powershell as an administrator

You will need to change Machinename\sql2019 to your machine and instance name

The script is simple.. it's for a one time use.. if you need to run the script repeatedly, you probably want to make it so you can rerun it for different databases and servers, so no hardcoding  :-)

Import-Module SqlServer -Version 21.0.17279

cd SQLSERVER:\SQL\MAchinename\sql2019\Databases\AdventureWorks2017\StoredProcedures



foreach ($tbl in Get-ChildItem  )

{
$k="C:\Temp\StoredProcedures\" + $($tbl.Schema) + "." + $($tbl.name) + ".SQL"
$tbl.Script() > $k
}

cd ..\UserDefinedFunctions

foreach ($tbl in Get-ChildItem  )

{
$k="C:\Temp\UserDefinedFunctions\" + $($tbl.Schema) + "." + $($tbl.name) + ".SQL"
$tbl.Script() > $k

That's all for the script.. if it ran successfully, you should see a bunch of procs and user defined functions in their directories.  Here is a view of the functions

Next up..installing SonarQube, SonarQube Scanner and the sonar-tsql-plugin