Script SQL Server items for git

Using SSMS, place the mouse over the database you want to script and press the right mouse button. On the context menu select Tasks and you will see that other menu pops up. Press the Generate Scripts item as can be seen on the image below.

In the Choose Objects screen pick the items that you want to script.

After hitting the next button on the next screen make sure to select the following options

In “Save as script file“ select “One script file per object”

In “Directory Name” enter the path to the folder where you want to store the files. Optionally you can mark the checkbox “Overwrite existing file”

Be sure to select the ANSI text radio button for the Save as option.

Before advancing in the wizard, you need to press the Advanced button in the upper right of the window and set the options Include Descriptive Headers and Script USE DATABASE to False as in the following screen capture

After finishing the wizard open the folder and check the files. You will notice that they were created with a name format composed by schema.ObjectName.ObjectType.sql

To get rid of the .ObjectType.sql tag follow this procedure.

Create the following subfolders

SP
UDF
UDT
Views

Open a PowerShell windows on the base folder (i.e. the folder holding the scripted files) and execute the following

md SP

md UDF

md UDT

md Views

The next step is to move the files according their type to the corresponding folder.

move *.StoredProcedure.sql .\SP

move *.UserDefinedFunction.sql .\UDF

move *.UserDefinedTableType.sql .\UDT

move *.View.sql .\Views

Then we can use the following script to rename the files

Get-ChildItem -Path .\SP -File -Recurse  | foreach-object -process { move $_.FullName $_.FullName.replace('.StoredProcedure.sql', '.sql')  }

 Get-ChildItem -Path .\UDF -File -Recurse  | foreach-object -process { move $_.FullName $_.FullName.replace('.UserDefinedFunction.sql', '.sql')  }

 Get-ChildItem -Path .\UDT -File -Recurse  | foreach-object -process { move $_.FullName $_.FullName.replace('.UserDefinedTableType.sql', '.sql')  }

 Get-ChildItem -Path .\Views -File -Recurse  | foreach-object -process { move $_.FullName $_.FullName.replace('.View.sql', '.sql')  }

The last step is to change the CREATE statement to use the new syntax CREATE OR ALTER. For this purpose, we will use notepad++ or the text editor of your choice.

In Notepad++ go to the Search menu and select “Find in Files”.

A window will pop up on which you need to set the text to be searched in the “Find what:” and need to set the replacement text in the “Replace with:” textbox. Also, you need to specify the folder that contains the files which should be any of the folders created in the previous step.

Use for example “CREATE VIEW” as the text to be searched and “CREATE OR ALTER VIEW” as the text to be replaced with. Then hit the Replace in Files button.