Database Source Code Output

Today the development databases are inaccessible. The network team perform a migration over the weekend and now most of the development teams are unable to do their work. I've taken this opportunity to note something down. Its a mechanic I've used for a number of years when working in a Windows environment.

First, a bit of context. I spent several years working with Oracle in Linux and Unix. For those years, I made use of parametrized shell scripting and SQLPLUS to make short work analytical queries for the purposes of resolving domain/business problems quickly without the need to write out SQL. I had become accustomed to scripting in a Linux environment and automating my tasks. Moving to a Windows environment effectively clipped my wings as I was no longer able to use scripts and work with immediacy. Or so I thought. Within a week of working with SQL server, I made a fantastic transition into using Linux shell scripts in the windows environment using Linux emulator and SQLCMD. One of the positive effects is that I can make use of native Windows applications through the shell script. One of these little inventions is detailed below. This method can also be used with SQLPLUS in Windows.

Enter SourceDump
The sourcedump function is one that takes in a module name and dumps out the entire obejct into a .sql file. Once dumped, it is opened with Notepad++.
1. The first part required is a script that dumps the module out. This is a SQL database so I make use of sys.all_sql_modules instead of Oracle’s dba_source.

set nocount on
use $(Database)
go

select top 1 
       definition
  from sys.all_sql_modules (nolock)
 where object_name(object_id) like '%$(search_phrase)%'
   and object_id > 0

I place this query into a file called
c:\mine\sql\r_sql_source_dump.sql

2. The next task is to have a folder to store the files output from the script.
c:\mine\sourcedump

Once these 2 requirements are met, I move onto the shell configuration and functions.
 
 
3. In the Linux emulator, open the .profile file with vim editor
[rayb.] ➤ vim $HOME/.profile

2. Adjust the $PATH variable to increase its scope to key SQL directories (SQLCMD + DLL's) and include a text editor (Notepad++ is what I make use of)
export PATH=$PATH:"/drives/c/Program Files/Notepad++":"/drives/c/Program Files (x86)/Microsoft SQL Server/Client SDK/ODBC/130/Tools/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/Tools/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/DTS/Binn/":"/drives/c/Program Files (x86)/Microsoft SQL Server/130/Tools/Binn/ManagementStudio/":"/drives/c/Program Files/TortoiseSVN/bin":"/drives/c/Users/rayb/AppData/Local/Microsoft/WindowsApps"

3. Add the following shell function to the .profile file
sourcedump()
{
  sqlcmd -y 0 -w 65000 -S "<SERVER_IP>,<INSTANCE_PORT>" -v Database="$1" -U sa -P <PASSWORD> -v search_phrase="$2" -o "c:\\mine\\sourcedump\\$2.sql" -i c:\\mine\\sql\\r_sql_source_dump.sql
  notepad++ "c:\\mine\\sourcedump\\$2.sql"
}

4. Resource the .profile file
[2017-05-22 09:14.04]  ~
[rayb.       ] ➤ source $HOME/.profile

[2017-05-22 09:14.09]  ~
[rayb.        ] ➤

5. Output a module