This project is read-only.

SQLDevTools Module Version 1 Released 10/21/2011:

It's functional. I contains a total of 44 PowerShell functions with 40 of them include help topics. Also, I have uncluded Aliases, in case you want to use them.

Sample Script using the Write-TSQLScriptInsertSelect function:

$SourceSQLInstanceName = "YourLocalSQLInstance";
$DestinationSQLInstanceName = "OtherSQLInstance\Server10";
$SourcedbName = "AdventureWorks";
$DestinationdbName = "YourDevDatabase";
$TableToCopy = "HumanResources.Employee","Purchasing.PurchaseOrderDetail","Sales.SalesPerson";

Example to create a T-SQL InsertSelect statements:
[Array] $InsertSelect = Write-TSQLScriptInsertSelect -SourceSQLInstanceName $SourceSQLInstanceName `
-DestinationSQLInstanceName $DestinationSQLInstanceName `
-SourcedbName $SourcedbName -DestinationdbName $DestinationdbName -tableName $TableToCopy;

Note: Don't forget to check out both Test-* functions because it gives you the path of automating the scripts

Use PowerShell to list all functions from this module:

(gcm -Module SQLDevTools -CommandType Function) | Select name

Few more examples using the SQLDevTools to script User Stored-Procedures:

Write-TSQLScriptUserStoredProcedure -SQLInstanceName ISO-DESKTOP-65 -SourcedbName AdventureWorks `
-DestinationdbName AdventureWorks -UserStoredProcedureName "dbo.uspGetBillOfMaterials" `
-SaveToFile "C:\temp\TestUSP.sql" -CreateUserStoredProcedureOnly

Write-TSQLScriptUserStoredProcedure -SQLInstanceName ISO-DESKTOP-65 -SourcedbName AdventureWorks `
-DestinationdbName AdventureWorks -UserStoredProcedureName "dbo.uspGetBillOfMaterials" `
-DropUserStoredProcedureOnly

Here's the list SQLDevTools Version 1:

Name
Clear-TSQLTruncateAllTables
Get-MSSQLVersion
Get-TSQLDatabaseRecoveryModel
Get-TSQLDatabaseTables
Get-TSQLTableColumns
Get-TSQLTableConstraints
Get-TSQLTableDFConstraints
Get-TSQLTableForeignKeys
Get-TSQLTableIdentity
Get-TSQLTableIndexes
Get-TSQLTableRecordCount
Get-TSQLTableTriggers
Get-TSQLUserDefinedDataTypes
Get-TSQLUserDefinedFunctions
Get-TSQLUsersDatabaseViews
Get-TSQLUsersStoredProcedures
Get-TSQLXmlSchemaCollections
New-IderaVirtualDB
Remove-IderaVirtualDB
Rename-TSQLScriptedIndexName
Rename-TSQLScriptedTableName
Reset-TSQLDatabaseAllProcesses
Set-TSQLDatabaseRecoveryModel
Show-AnyObject2Grid
Show-AnyObject2Html
Show-Member2Grid
Show-Member2Html
Show-MsgBox
Test-TSQLCopyTable
Test-TSQLCopyToAnotherInstance
Write-TSQLScriptCKConstraint
Write-TSQLScriptCreatedbSchema
Write-TSQLScriptDFConstraint
Write-TSQLScriptForeignKey
Write-TSQLScriptIndex
Write-TSQLScriptInsertSelect
Write-TSQLScriptTable
Write-TSQLScriptTrigger
Write-TSQLScriptUserDefinedDataTypes
Write-TSQLScriptUserDefinedFunctions
Write-TSQLScriptXmlSchemaCollections
Write-TSQLUsersDatabaseViews
Write-TSQLUsersStoredProcedures


This first release will not include an installer. It's a zipped file. This module was build using SAPIEN's PrimalForms 2011.

This module need to be set in your User "WindowsPowerShell\Modules" folder.

Then, to load *module use:
Import-Module SQLDevTools

To remove module use:
Remove-Module SQLDevTools

Again, you must have SQL Server 2008 (or above). or the standalone version of "SQL Server Management Studio 2008 R2 " installed in your computer.

*note:
If you're using SQL Server 2008, you need to get Chad Miller "SQLPS" module:

a) To Download Chad Miller SQLPS module for SQL Server 2008 and 2008 R2:
http://sev17.com/2010/07/making-a-sqlps-module/

Or use SQL Server Denali CTP3 which includes the SQLPS module.

Then, to load modules use:
Import-Module SQLPS -Disablenamechecking # for SQL Server 2008, 2008 R2, and Denali CTP3

Keep in mind, 39 of the 43 functions will include Help topics
The following functions doesn't have Help topics yet:
Show-AnyObject2Grid
Show-AnyObject2Html
Show-Member2Grid
Show-Member2Html

Displaying full list of this module Functions and Aliases usign the "get-command" alias:

gcm -Module sqldevtools | Ft name,CommandType -Auto

Name CommandType
Clear-TSQLTruncateAllTables Function
ctat Alias
Get-MSSQLVersion Function
Get-TSQLDatabaseRecoveryModel Function
Get-TSQLDatabaseTables Function
Get-TSQLTableColumns Function
Get-TSQLTableConstraints Function
Get-TSQLTableDFConstraints Function
Get-TSQLTableForeignKeys Function
Get-TSQLTableIdentity Function
Get-TSQLTableIndexes Function
Get-TSQLTableRecordCount Function
Get-TSQLTableTriggers Function
Get-TSQLUserDefinedDataTypes Function
Get-TSQLUserDefinedFunctions Function
Get-TSQLUsersDatabaseViews Function
Get-TSQLUsersStoredProcedures Function
Get-TSQLXmlSchemaCollections Function
gmsv Alias
gsusp Alias
gtc Alias
gtcn Alias
gtdbr Alias
gtdt Alias
gtfk Alias
gtidn Alias
gtix Alias
gtrc Alias
gtsdbv Alias
gtsdf Alias
gtsuddt Alias
gtsudf Alias
gtsxsc Alias
gtts Alias
New-IderaVirtualDB Function
nvdb Alias
Remove-IderaVirtualDB Function
Rename-TSQLScriptedIndexName Function
Rename-TSQLScriptedTableName Function
Reset-TSQLDatabaseAllProcesses Function
rtdp Alias
rtsix Alias
rtstn Alias
rvdb Alias
sag Alias
sah Alias
Set-TSQLDatabaseRecoveryModel Function
Show-AnyObject2Grid Function
Show-AnyObject2Html Function
Show-Member2Grid Function
Show-Member2Html Function
Show-MsgBox Function
smb Alias
smg Alias
smh Alias
stdbr Alias
tctai Alias
Test-TSQLCopyTable Function
Test-TSQLCopyToAnotherInstance Function
tsct Alias
Write-TSQLScriptCKConstraint Function
Write-TSQLScriptCreatedbSchema Function
Write-TSQLScriptDFConstraint Function
Write-TSQLScriptForeignKey Function
Write-TSQLScriptIndex Function
Write-TSQLScriptInsertSelect Function
Write-TSQLScriptTable Function
Write-TSQLScriptTrigger Function
Write-TSQLScriptUserDefinedDataTypes Function
Write-TSQLScriptUserDefinedFunctions Function
Write-TSQLScriptXmlSchemaCollections Function
Write-TSQLUsersDatabaseViews Function
Write-TSQLUsersStoredProcedures Function
wsudv Alias
wtscc Alias
wtsdf Alias
wtsds Alias
wtsfk Alias
wtsi Alias
wtsis Alias
wtst Alias
wtstg Alias
wtsuddt Alias
wtsudf Alias
wtsxsc Alias


Last edited Nov 14, 2011 at 7:27 PM by MaxTrinidad, version 19

Comments

No comments yet.