Powershell and SMO scripting – Part 3

Published 22 August 09 05:04 PM | MartinBell

Madhivanan posted a blog entry on how to script table definitions and data the other day. For SQL 2008 this gave a link that showed you how to use the scripting wizard to generate this script.

If you have ever posted a problem on the newsgroups or forums when you can't get a SELECT statement to return the data you want; without posting DDL and sample data your first reply may well have been one asking you to post these and may have also pointed you to Aaron Bertrands web site on how to do it. Taking time to do this, along with showing that you have already put some effort into answering the problem is appreciated by the people (like myself) who answer these posts. It removes the need for them to generate their own tables and test data and reduces the chance of something being mis-interpretted. Posting INSERT statements is far more useful than posting a table of data, which the replier would have to convert into something that could be used. Posting DDL and INSERT statements will not only increase the chance of the post being answered, but it is more likely to be answered correctly (assuming that the question is also correct!).

If you checked out my previous Powershell scripting articles or have used SMO to script table previously you will recognise the similarity between the option available in the Choose Script Options dialog and the properties of the SMO ScriptingOptions object, although looking at the latest version of Books Online the option to script data is not mentioned.

The ScriptingOptions class has a ScriptData property, which if set to true will return the data as insert statements. Unfortunately my previous script which calls the scripting option with a single argument i.e.

$Scripter.Script($Item)

does not support scripting data and you will get the error:

Exception calling "Script" with "1" argument(s): "This method does not support
scripting data."
At line:12 char:17
+ $Scripter.Script( <<<< $Item)


if you try to set the ScriptData property to $True for the scriptingoptions. The solution is not to use the Script method, but instead use the EnumScript method. So the resulting script is:

cd SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables
$Scripter=new-object ("Microsoft.SqlServer.Management.Smo.Scripter") ("(local)")
$Scripter.Options.DriAll=$True
$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.ScriptData=$False;
$Scripter.Options.IncludeIfNotExists=$True;
$Scripter.EnumScript($Item)
$Scripter.Options.ScriptDrops=$False;
$Scripter.Options.ScriptData=$True;
$Scripter.Options.IncludeIfNotExists=$False;
$Scripter.Options.AppendToFile=$True
$Scripter.EnumScript($Item)
}

This will produce output like this:

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
SET IDENTITY_INSERT [dbo].[AWBuildVersion] ON

GO
INSERT [dbo].[AWBuildVersion] ([SystemInformationID], [Database Version], [VersionDate], [ModifiedDate]) VALUES (1, N'10.00.80404.00', CAST(0x00009A7400000000 AS DateTime), CAST(0x00009A7400000000 AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[AWBuildVersion] OFF
GO
ALTER TABLE [dbo].[AWBuildVersion] ADD  CONSTRAINT [DF_AWBuildVersion_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

Filed under: , ,

Comments

# Dew Drop – August 23, 2009 | Alvin Ashcraft's Morning Dew said on August 23, 2009 09:44 PM:

Pingback from  Dew Drop &ndash; August 23, 2009 | Alvin Ashcraft's Morning Dew

# Dew Drop – August 23, 2009 | Alvin Ashcraft's Morning Dew said on August 23, 2009 09:44 PM:

Pingback from  Dew Drop &ndash; August 23, 2009 | Alvin Ashcraft's Morning Dew

# http://nxy.in/ said on November 20, 2014 09:34 AM:

Powershell and SMO scripting – Part 3 - Martin Bell UK SQL Server MVP

This Blog

SQL Blogs

Syndication