利用powershell 生成sqlserver 对象脚本

介绍

本篇将介绍如何使用powershell 生成脚本。

解决

利用 Script() 方法

首先，创建一个server 对象实例，然后连接它。我只需要引入Script() 方法，脚本如下：

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1"  

$dbs=$s.Databases 

$dbs["Northwind"].Script()

如上，这个脚本将生成sql 脚本，当然这也意味着我们会把所有脚本生成出来，然后调用脚本把结果输出到外部文件内。

$dbs["Northwind"].Script() | Out-File D:\PSScripts\Northwind.sql

文件内容如下:

CREATE DATABASE [Northwind] ON  PRIMARY  
( NAME = N'Northwind', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Northwind.mdf' , 
SIZE = 3456KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
 LOG ON  
( NAME = N'Northwind_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Northwind_1.ldf' , 
SIZE = 4224KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
 COLLATE SQL_Latin1_General_CP1_CI_AS 
EXEC dbo.sp_dbcmptlevel @dbname=N'Northwind', @new_cmptlevel=90 
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 
BEGIN 
EXEC [Northwind].[dbo].[sp_fulltext_database] @action = 'disable' 
END 
ALTER DATABASE [Northwind] SET ANSI_NULL_DEFAULT OFF  
ALTER DATABASE [Northwind] SET ANSI_NULLS OFF  
ALTER DATABASE [Northwind] SET ANSI_PADDING OFF  
ALTER DATABASE [Northwind] SET ANSI_WARNINGS OFF  
ALTER DATABASE [Northwind] SET ARITHABORT OFF  
ALTER DATABASE [Northwind] SET AUTO_CLOSE OFF  
ALTER DATABASE [Northwind] SET AUTO_CREATE_STATISTICS ON  
ALTER DATABASE [Northwind] SET AUTO_SHRINK OFF  
ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS ON  
ALTER DATABASE [Northwind] SET CURSOR_CLOSE_ON_COMMIT OFF  
ALTER DATABASE [Northwind] SET CURSOR_DEFAULT  GLOBAL  
ALTER DATABASE [Northwind] SET CONCAT_NULL_YIELDS_NULL OFF  
ALTER DATABASE [Northwind] SET NUMERIC_ROUNDABORT OFF  
ALTER DATABASE [Northwind] SET QUOTED_IDENTIFIER OFF  
ALTER DATABASE [Northwind] SET RECURSIVE_TRIGGERS OFF  
ALTER DATABASE [Northwind] SET  DISABLE_BROKER  
ALTER DATABASE [Northwind] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
ALTER DATABASE [Northwind] SET DATE_CORRELATION_OPTIMIZATION OFF  
ALTER DATABASE [Northwind] SET TRUSTWORTHY OFF  
ALTER DATABASE [Northwind] SET ALLOW_SNAPSHOT_ISOLATION OFF  
ALTER DATABASE [Northwind] SET PARAMETERIZATION SIMPLE  
ALTER DATABASE [Northwind] SET READ_COMMITTED_SNAPSHOT OFF  
ALTER DATABASE [Northwind] SET  READ_WRITE  
ALTER DATABASE [Northwind] SET RECOVERY FULL  
ALTER DATABASE [Northwind] SET  MULTI_USER  
ALTER DATABASE [Northwind] SET PAGE_VERIFY TORN_PAGE_DETECTION   
ALTER DATABASE [Northwind] SET DB_CHAINING OFF

生成表的脚本

我们能遍历这个表在数据库中，然后再次调用如下:

#Generate script for all tables

foreach ($tables in $dbs["Northwind"].Tables) 
{
    $tables.Script() + "`r GO `r " | out-File D:\PSScripts\Scripts.sql -Append
}

注意：

引入已经包含了回车符号‘r’，以便能够正确地显示结果.Script() 返回每一个办好的对象生成脚本。
添加GO语句，将每个CREATE TABLE语句视为一个批处理。因为Script()方法将生成CREATE TABLE语句，所以即使没有GO语句，也不会有太大的问题。但是，如果它将为其他数据库对象(如视图和存储过程)生成脚本，那么您肯定需要包含一个GO语句，以便将单个的CREATE语句作为批处理处理。
我使用Out-File cmdlet的-Append参数将Script()方法的输出附加到现有文件中。

生成索引脚本

既然我们已经为创建表生成了脚本，那么我们也可以为表中相应的索引生成脚本。我们只需要遍历Indexes集合，再次调用Script()方法，并将结果添加到输出文件中

#Generate script for all tables

foreach ($tables in $dbs["Northwind"].Tables) 
{
    $tables.Script() + "`r GO `r " | out-File D:\PSScripts\Scripts.sql -Append

    #Generate script for all indexes in the specified table
    foreach($index in $tables.Indexes)
    {
        $index.Script() + "`r GO `r" | out-File D:\PSScripts\Scripts.sql -Append
    }
}

生成的SQL脚本的一部分如下所示。注意，在使用SMO的Windows PowerShell中，只需几行代码就可以轻松地生成SQL脚本。

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER ON 
CREATE TABLE [dbo].[Categories]( 
   [CategoryID] [int] IDENTITY(1,1) NOT NULL, 
   [CategoryName] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
   [Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
   [Picture] [image] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] 
 GO  
CREATE NONCLUSTERED INDEX [CategoryName] ON [dbo].[Categories]  
( 
   [CategoryName] 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

使用脚本对象生成器

生成SQL脚本的另一个选项是使用Scripter对象。Scripter对象是SQL Server中管理脚本操作的总体顶级对象。因为它是一个完整的对象，所以它比Script()方法有更多的功能。例如，它能够发现对象之间的关系，并为您提供更多类似于使用SQL Server Management Studio的脚本选项。

在下面Windows PowerShell脚本中，我们将使用Microsoft.SqlServer.Management.Smo.Scripter namespace这个新对象。

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

其中$s是我们之前定义的Server对象的一个实例。一旦定义了Scripter对象，就可以指定不同的Options属性。这些属性定义了Scripter对象如何生成SQL脚本。下面是我从SQL Server Management Studio中选取的一些选项作为示例。

$scrp.Options.AppendToFile = $True 
$scrp.Options.ClusteredIndexes = $True 
$scrp.Options.DriAll = $True 
$scrp.Options.ScriptDrops = $True  
$scrp.Options.IncludeHeaders = $True 
$scrp.Options.ToFileOnly = $True 
$scrp.Options.Indexes = $True 
$scrp.Options.WithDependencies = $True 

$scrp.Options.FileName = "D:\PSScripts\NWind.SQL"

下面是脚本涉及的属性的简要描述

AppendToFile - specifies whether to append the script to the output file or overwrite it. It's the equivalent of what we did with the Out-File cmdlet using the -append parameter
ClusteredIndexes - specifies whether to include the clustered index definitions
DriAll - specifies whether to include the DRI - declarative referential integrity - objects
ScriptDrops - specifies whether to include the IF EXISTS..DROP statements
IncludeHeaders - specifies whether to include header information llike the date and time the script was generated
ToFileOnly - specifies whether to simply generate the script to a file or store it in a string as an output as well
Indexes - specifies whether to include indexes
WithDependencies - specifies whether to include all other dependent objects in the script

这里的关键属性是WithDependencies属性，因为这个属性可以为所有其他对象(如视图和存储过程)生成脚本，这些对象都依赖于您想要用脚本输出的对象。不像简单地使用Script()方法时，需要显式地调用所有其他对象集合来生成相应的脚本，这个属性告诉Scripter对象只需查看关系和对象依赖关系，作为生成脚本的基础。

最后，我们需要调用Scripter对象的Script()方法，传递我们想要为其生成脚本的数据库对象集合

$scrp.Script($dbs["Northwind"].Tables)

使用Scripter对象的完整代码清单如下所示。

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')| out-null

# Create an SMO connection to the instance
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST\SQL2005_1" 

$dbs = $s.Databases
$dbs["Northwind"].Script() | Out-File D:\PSScripts\NWind.SQL

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $True
$scrp.Options.IncludeIfNotExists = $True
$scrp.Options.IncludeHeaders = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True

$scrp.Options.FileName = "D:\PSScripts\NWind.SQL"
$scrp.Script($dbs["Northwind"].Tables)

posted @ 2022-03-27 20:50 DB乐之者阅读(421) 评论(0) 编辑收藏举报

刷新页面返回顶部

Stay hungery

利用powershell 生成sqlserver 对象脚本

介绍

解决

利用 Script() 方法

生成表的脚本

生成索引脚本

使用脚本对象生成器

公告