Powershell Script - Further Changes

For @ibrahim20:

(Read it in the word document attached for better formatting)

Based on what is already done in the previous project, I look for following new requirements:

- Add Server, Database and BasePath in configuration file. Review Sample Configuration file.

- In current design, I can have ONLY ONE StartsWith, ONLY ONE EndsWith and ONLY ONE Contains. This is restrictive. For example I want StartsWith “ABC” or StartsWith “DEF”. So following is proposed:

- Use a list (array) of Tasks. Each Task is a scripting task.

o ObjectType can be Table or View or StoredProcedure or Funciton

o Filter should be Regex expression (always use -match and not -like). (e.g. Expression starting with ABC should be “^ABC”. Expression Ending with ABC should be “ABC$” and contains will become “ABC”.

o Location Filename will be name of the object .txt and it will be stored in $BasePate + $Location.

 If the folder does not exist, then create it.

 If the file already exists, then overwrite it (instead of append).

- I also want to define Scripting Options in the config file. Following Scripting Options are important: (Reference: [login to view URL])

o AnsiFile

o AnsiPadding

o ScriptData

o ScriptSchema

o ScriptForAlter

o ScriptForCreateDrop

o Indexes

o Triggers

I don’t know what is the best way to put Scripting Options in the config file, so that its easy to use. But I want each task to have own set of scripting options.

o For example, I want to have one task to ScriptData (of filtered set of tables).

o I may want to script Alter statements instead of Create Statements.

o I may want to script Drop (if exists) statements before Create options.

o I want to script all constraints and triggers.

(We can discuss on this)

Attached is an example of how a table is scripted by current PS script and how I did it in SQL Server Management Studio, using Options from “Generate Scripts” dialog and selecting the options. I need to script the way its done by “Generate Scripts”.

The Ultimate Goal:

o My goal is to run this script and output files in a specific folder.

o This script should be scheduled to run every night.

o A next piece of script (not included in this task/project) should push the generated code (scripts) into a GIT repository and commit. All changed files should get committed with new version. If script is exactly same as what it was in last script generation, then no GIT will keep the old version.

Server Microsoft SQL Powershell

ID Proiect: #28920408

Detalii despre proiect