利用powershell 生成sqlserver 对象脚本
2022/3/28 2:23:34
本文主要是介绍利用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)
这篇关于利用powershell 生成sqlserver 对象脚本的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-01-08Docker下的SqlServer发布订阅启用
- 2023-06-05Docker安装MS SQL Server并使用Navicat远程连接
- 2023-05-25深入浅出 SQL Server CDC 数据同步
- 2023-05-12通过空间占用和执行计划了解SQL Server的行存储索引
- 2023-04-24以SQLserver为例的Dapper详细讲解
- 2022-11-30SQL server高级函数查询
- 2022-11-26SQL SERVER数据库服务器CPU不能全部利用原因分析
- 2022-11-21SQL Server 时间算差值/常用函数
- 2022-11-20调试Archery连接SQL Server提示驱动错误
- 2022-10-22SQL Server 完整、差异备份+完整、差异还原(详细讲解,规避错误)