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
No comments:
Post a Comment