利用powershell 生成sqlserver 对象脚本 |
您所在的位置：网站首页 › sql脚本生成器 › 利用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)
|
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |