Modify Columns and Stored Procedures in PowerShell

How many times have you run into a case where your database design is "almost" right?  Maybe you're upgrading from an earlier version of SQL Server and want to take advantage of some new features after the upgrade. Maybe you realize after implementation that you have some limitations in your current schema that just need to be fixed.

Related: Create Database Tables in PowerShell with SMO

One of the benefits of understanding the Server Management Objects (SMO) model is the realization that you can make changes across multiple objects, like we did in a previous post, where we added new columns to all the tables in a database.

Imagine, we realized that after an upgrade from SQL Server 2000, that we have a lot of "notes"-type columns in our databases with a datatype of varchar(2000), for example. SQL Server 2005 introduced the varchar(max) datatype, and it allows us to put up to 2GB of notes into our database. It's important that your database structures support the needs of the application, and sometimes you need to put more than 2000 characters in your notes.

So, the task is to take all columns in our database that have more than 1999 characters defined and turn that into a varchar(max) definition. Note that the (max) designator makes the column a Large Object (LOB) type column, which may impact performance and database management in different ways, but that discussion is outside the scope of this discussion.

Change the MaximumLength Property

It turns out that the way you specify the (max) designator is to change the MaximumLength property of the column from its current value (2000, in our example) to the value -1. After we connect to the server and database, we'll iterate through all the tables, and for each column we find that has the MaximumLength greater than 1999, we'll set it to -1, and alter the table.

# Connect to the specified instance$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst # Get the specified database where the changes will be made$db = $s.Databases[$database] # Get the tables collection for the database$tbs = $db.Tables # Iterate through the tablesforeach($tb in $tbs) {$tbnm = $tb.Namewrite-output "Table: $tbnm"$chgd = $False # Get the columns collection and iterate through them$cols = $tb.Columnsforeach ($col in $cols) {$colnm = $col.Nameif ($col.DataType.MaximumLength -gt 1999) {$col.DataType.MaximumLength = -1 write-output "Changed Column: $colnm"$chgd = $True}} # Only alter the table if datatypes have changedif ($chgd -eq $True) {$tb.Alter()} }

Now, the application can record more than 2000 characters without the loss of possibly important information.

StoredProcedure Object Property Called TextBody

Another thing that sometimes happens is the redefinition of columns so that the original column no longer contains what we need in a stored procedure, but a new column does, or a misspelling was made in a table and you want to correct that across all the stored procedures in the database.

The StoredProcedure object has a property called TextBody, which contains all the code for the stored procedure. TextBody is a System.String object, and that object has a method called Contains to tell us whether the string is present and another called Replace which allows us to do the work we need to do. In the script, we pass in the instance and database where we want to make the change, and the original string value along with its replacement value. In each stored procedure in the database we then search for the original value and, if found, replace it and alter the stored procedure.

# Connect to the specified instance$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst # Get the specified database where the changes will be made$db = $s.Databases[$database] $procs = $db.StoredProceduresforeach ($proc in $procs) {if($proc.IsSystemObject -eq $False) {$txt = $proc.TextBody$chgd = $False # Check to see if the text of the proc contains the search string and replace it if soif ($txt.Contains($srch) -eq $True) {$txt = $txt.Replace($srch,$repl)$chgd = $True} # If a change occurred, set the updated TextBody property and alter the procif ($chgd = $True) {$proc.TextBody = $txt$proc.Alter()}}}

Having tools like SMO to make changes across multiple objects allows us far greater flexibility to do the kinds of work that can sometimes take us far too long to do in T-SQL.

Related: Stored Procedure Retrieves Information About Partitioned Tables