I've mentioned before my company manages trade shows, and we've got a series of web sites managed by an application which uses a different SQL Server database for each site, with a master database (I'll call it Global, to differentiate it from SQL Server's master database). Well, we have a number of shows which have multiple show locations, and these use a parent-child set of databases, where information pertaining to all the shows is in the parent database, and then information to each specific location is in the child database.
There is information that is important to both parent and child, and that's kept in the parent database, and we have views in the child which return the data from the parent database that's specific to the child. The problem is when the application is changed, and the views need to be modified. Each child site has a technically different view based on it's show ID and the name of the parent database.
Yesterday I had to make such a change, and I decided a PowerShell script was the best way to approach the problem. Now, there's a table defining all the parent/child relationships in the Global database, so the first step is to query that table to get the necessary information:
#build_exibitors_view.ps1
#This script will recreate the 'exhibitors' view in all child databases
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=MyServer\MyInstance;Integrated Security=TRUE;Initial Catalog=Global");
$ds = new-object "System.Data.DataSet" "dsChildSites"
$q = "SELECT [childShowID]"
$q = $q + " ,[parentDBName]"
$q = $q + " ,[childDBName]"
$q = $q + " FROM [Global].[dbo].[ParentChild]"
$da = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$da.Fill($ds)
Now, once the DataSet is populated it's time to loop through the results building the new view for each child database. I create a DataTable from the DataSet, then use the FOREACH-OBJECT cmdlet to step through the results, and then create variables for the values returned for each iteration.
$dtChild = new-object "System.Data.DataTable" "dsChildSites"
$dtChild = $ds.Tables[0]
$dtChild | FOREACH-OBJECT {
$pDB = $_.parentDBName
$cDB = $_.childDBName
$cshowID = $_.childShowID
Inside the loop I need to connect to each child database and first drop the existing view. I concatenate the name of the child database to the connection string for the SqlCommand object.
$cn = new-object System.Data.SqlClient.SqlConnection("Data Source=MyServer\MyInstance;Integrated Security=TRUE;Initial Catalog=" + $cDB)
$cn.Open()
$sql = "IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[exhibitors]')) DROP VIEW [dbo].[exhibitors]"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($sql, $cn)
$cmd.ExecuteNonQuery() | out-null
Once the existing view is deleted I can create the new one. Here I concatenate the name of the parent database in the FROM clause, and filter the resultset based on the showID of the child show.
$sql = "CREATE VIEW [dbo].[exhibitors]"
$sql = $sql + " AS"
$sql = $sql + " SELECT exhibID, name, description"
$sql = $sql + " FROM " + $pDB + ".dbo.exhibitors"
$sql = $sql + " WHERE (showID = " + [string]$cshowID + ")"
Now that the view has been built for the child database I can execute the query to create it.
$cmd2 = new-object "System.Data.SqlClient.SqlCommand" ($sql, $cn)
$cmd2.ExecuteNonQuery() | out-null
$cn.Close()
}
(Note that the actual view used is much more complicated than this, but I wanted to share the technique.) Rather than use an editor to make changes to a couple of dozen views across as many databases I used PowerShell to automate the process and made the changes in a few seconds.
Allen