Powershell and SMO scripting – Part 2

Published 15 August 09 06:00 PM | MartinBell

In my last post I created a script that will generate files for each view that contain a drop and create statement for the view. If you wished to script all tables in the database then all you would have to do is change the location you have set:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}

I have also changed the way the filename is created by pre-pending the schema name to the filename.

So what if I wanted to only generate scripts for a given schema?

To do that modify the foreach statement so that the returned object is piped through a Where-Object to filter those objects with the schema property set to “Sales”:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem | Where {$_.Schema -eq "Sales" } ) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Schema + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}

If you examine the script created e.g.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Sales].[SalesPerson]') AND type in (N'U'))
DROP TABLE [Sales].[SalesPerson]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Sales].[SalesPerson](
    [SalesPersonID] [int] NOT NULL,
    [TerritoryID] [int] NULL,
    [SalesQuota] [money] NULL,
    [Bonus] [money] NOT NULL,
    [CommissionPct] [smallmoney] NOT NULL,
    [SalesYTD] [money] NOT NULL,
    [SalesLastYear] [money] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

You may notice that there are no primary keys, foreign keys and other constraint are created in the scripts. This is because the DRIAll property is set to false. Setting this to true will give you a script with all the DRI for the table:

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AWBuildVersion_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[AWBuildVersion] DROP CONSTRAINT [DF_AWBuildVersion_ModifiedDate]
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AWBuildVersion]') AND type in (N'U'))
DROP TABLE [dbo].[AWBuildVersion]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AWBuildVersion](
    [SystemInformationID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Database Version] [nvarchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
    [VersionDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_AWBuildVersion_SystemInformationID] PRIMARY KEY CLUSTERED
(
    [SystemInformationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[AWBuildVersion] ADD  CONSTRAINT [DF_AWBuildVersion_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

If you are putting these files into source code control having primary keys, foreign keys and constraints may not be what you want. This does present a small problem of how to script indexes, foreign keys etc. separately as they are located below each table and not the database. This can be seen by changing the location to be the database e.g.

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks

and do a Get-Childitem or dir and you can see that the following are listed:

ApplicationRoles
Assemblies
AsymmetricKeys
Certificates
DatabaseAuditSpecifications
Defaults
ExtendedProperties
ExtendedStoredProcedures
FileGroups
FullTextCatalogs
FullTextStopLists
LogFiles
PartitionFunctions
PartitionSchemes
PlanGuides
Roles
Rules
Schemas
ServiceBroker
StoredProcedures
SymmetricKeys
Synonyms
Tables
Triggers
UserDefinedAggregates
UserDefinedDataTypes
UserDefinedFunctions
UserDefinedTableTypes
UserDefinedTypes
Users
Views
XmlSchemaCollections

This does not include indexes or any table constraints. You need to change location to a given table e.g.

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables \person.contact

where Get-Childitem will return:

Checks
Columns
ExtendedProperties
ForeignKeys
FullTextIndex
Indexes
PartitionSchemeParameters
PhysicalPartitions
Statistics
Triggers

So we could get the indexes for a given table by using the previous script by setting the location to the indexes:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables\Person.Contact\Indexes

$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Item in Get-ChildItem ) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Parent.Schema + "_" + $Item.Parent.Name + "_" + $Item.Name + ".sql" $Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}

To produce files such like Person_Contact_AK_Contact_rowguid.sql (notice the schema and table names have been pre-pended):

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Person].[Contact]') AND name = N'AK_Contact_rowguid')
DROP INDEX [AK_Contact_rowguid] ON [Person].[Contact] WITH ( ONLINE = OFF )
GO
CREATE UNIQUE NONCLUSTERED INDEX [AK_Contact_rowguid] ON [Person].[Contact]
(
    [rowguid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

You may notice that primary keys are also scripted, usually these you may wish to script these into a different folder. To exclude these we can again use a Where-Object to filter them out, so the foreach statement becomes:

foreach ($Item in Get-ChildItem | where {$_.IndexKeyType -ne "DriPrimaryKey"} ) {

Ideally if I am scripting a large number of tables I don’t want to have to change location for each table. I can therefore script all the indexes by using a nested loop:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$False
$Scripter.Options.IncludeHeaders=$False
$Scripter.Options.ToFileOnly=$True
$Scripter.Options.WithDependencies=$False

foreach ($Table in Get-ChildItem ) { foreach ($Item in $Table.Indexes | where {$_.IndexKeyType -ne "DriPrimaryKey"} ) {
$Scripter.Options.FileName="C:\PowerShell\" + $Item.Parent.Schema + "_" + $Item.Parent.Name + "_" + $Item.Name + ".sql"
$Scripter.Options.AppendToFile=$False
$Scripter.Options.ScriptDrops=$True;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.Script($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.Script($Item)
}
}

If you only wanted to script clustered indexes then checking the IsClustered property of the index would include them e.g.

foreach ($Table in Get-ChildItem ) { foreach ($Item in $Table.Indexes | where {$_.IsClustered -eq "True"} ) {

or to script only non-clustered primary keys you could use:

foreach ($Table in Get-ChildItem ) { foreach ($Item in $Table.Indexes | where {$_.IndexKeyType -ne "DriPrimaryKey" -and $_.IsClustered -eq $False} ) {

Now you should have everything to script the entire database!

Filed under: , ,

Comments

# Powershell and SMO scripting ??? Part 1 - Martin Bell UK SQL Server MVP said on August 15, 2009 04:49 PM:

Pingback from  Powershell and SMO scripting ??? Part 1 - Martin Bell UK SQL Server MVP

# Dew Drop – August 15, 2009 | Alvin Ashcraft's Morning Dew said on August 17, 2009 02:25 PM:

Pingback from  Dew Drop – August 15, 2009 | Alvin Ashcraft's Morning Dew

# Martin Bell UK SQL Server MVP said on August 22, 2009 04:31 PM:

In SQL Server 2008 Management Studio you can now script a table definition plus the data as INSERT statements, so how can you do that programatically with Powershell? In the third in my series of investigations I will look at how it can be done.

# Martin Bell UK SQL Server MVP said on November 19, 2010 10:29 PM:

When it comes to column collations SMO doesn't readily do what I want it to do, so I thought I could use the power of Powershell to overcome it!

This Blog

SQL Blogs

Syndication