《PowerShell V3——SQL Server 2012数据库自动化运维权威指南》——2.12 创建表

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

本节书摘来自异步社区出版社《PowerShell V3—SQL Server 2012数据库自动化运维权威指南》一书中的第2章,第2.12节,作者:【加拿大】Donabel Santos,更多章节内容可以访问云栖社区“异步社区”公众号查看。

2.12 创建表

本方案展示了如何用PowerShell和SMO创建表。

2.12.1 准备

我们将在AdventureWorks2008R2数据库创建一个表Student,包含5列。为了更好地了解我们要完成什么,下面给出了等价的创建表的T-SQL脚本。

USE AdventureWorks2008R2
GO
CREATE TABLE [dbo].[Student](
[StudentID] [INT] IDENTITY(1,1) NOT NULL,
[FName] [VARCHAR](50) NULL,
[LName] [VARCHAR](50) NOT NULL,
[DateOfBirth] [DATETIME] NULL,
[Age] AS (DATEPART(YEAR,GETDATE())-DATEPART(YEAR,[DateOfBirth])),
CONSTRAINT [PK_Student_StudentID] PRIMARY KEY CLUSTERED 
(
 [StudentID] ASC
)
)
GO

2.12.2 如何做…

1.通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。

2.导入SQLPS模块,创建一个新的SMO服务器对象。

#import SQL Server module
Import-Module SQLPS –DisableNameChecking
#replace this with your instance name
$instanceName = "KERRIGAN"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
-ArgumentList $instanceName

3.接下来,添加如下代码配置数据库和表名,如果存在,则删除表。

$dbName = "AdventureWorks2008R2"
$tableName = "Student"
$db = $server.Databases[$dbName]
$table = $db.Tables[$tableName]
#if table exists drop
if($table)
{
   $table.Drop()
}

4.添加下面的脚本创建表,并运行。

#table class on MSDN
#http://msdn.microsoft.com/en-us/library/ms220470.aspx
$table = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table-ArgumentList
$db, $tableName
#column class on MSDN
#http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management smo.column.as
px
#column 1
$col1Name = "StudentID"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int;
$col1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList $table, $col1Name, $type
$col1.Nullable = $false
$col1.Identity = $true
$col1.IdentitySeed = 1
$col1.IdentityIncrement = 1
$table.Columns.Add($col1)
#column 2 – nullable
$col2Name = "FName"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::VarChar(50)
$col2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList $table, $col2Name, $type
$col2.Nullable = $true
$table.Columns.Add($col2)
#column 3 - not nullable, with default value
$col3Name = "LName"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::VarChar(50)
$col3 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList $table, $col3Name, $type
$col3.Nullable = $false
$col3.AddDefaultConstraint("DF_Student_LName").Text = "'Doe'"
$table.Columns.Add($col3)
#column 4 - nullable, with default value
$col4Name = "DateOfBirth"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime;
$col4 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList $table, $col4Name, $type
$col4.Nullable = $true
$col4.AddDefaultConstraint("DF_Student_DateOfBirth").Text = "'1800-00-00'"
$table.Columns.Add($col4)
#column 5
$col5Name = "Age"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int;
$col5 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column
-ArgumentList $table, $col5Name, $type
$col5.Nullable = $false
$col5.Computed = $true
$col5.ComputedText = "YEAR(GETDATE()) - YEAR(DateOfBirth)";
$table.Columns.Add($col5)
$table.Create()

5.让StudentID作为主键。

#########################################
#make StudentID a clustered PK
#########################################
#note this is just a "placeholder" right now for PK
#no columns are added in this step
$PK=New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index –ArgumentList
$table,"PK_Student_StudentID"
$PK.IsClustered =$true
$PK.IndexKeyType =[Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey
#identify columns part of the PK
$PKcol=New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn 
-ArgumentList $PK,$col1Name
$PK.IndexedColumns.Add($PKcol)
$PK.Create()

6.检查表是否被创建,有正确的列和约束。

(1)打开SSMS。

(2)展开AdventureWorks2008R2数据库的“Tables”。

(3)展开“dbo.Student”表的“Columns”、“Keys”、“Constraints”和“Indexes”。

image

2.12.3 如何实现…

创建表的第一步是创建一个SMO表对象。

$table = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table -ArgumentList $db,
$tableName
之后,在调用Microsoft.SqlServer.Management.SMO.Table类的方法之前,把所有将要被定义的列逐一添加到表中。

让我们一步步来。创建列时,我们首先需要识别存储在列的数据类型和列属性。

在SMO中列数据类型定义为Microsoft.SqlServer.Management.SMO.DataType。在枚举中每个T-SQL数据类型是相当有代表性的。如下格式显示了如何使用数据类型。

为了创建列,你需要定制表变量、数据类型和列名。

$col1Name = "StudentID"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::Int
$col1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.
Column -ArgumentList $table, $col1Name, $type

通常列属性对列变量是可访问的。常用的属性包括:

Nullable
Computed
ComputedText
Default Constraint(通过使用AddDefaultConstraint方法)

例如:

#column 4 - nullable, with default value
$col4Name = "DateOfBirth"
$type = [Microsoft.SqlServer.Management.SMO.DataType]::DateTime;
$col4 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.
Column -ArgumentList $table, $col4Name, $type
$col4.Nullable = $true
$col4.AddDefaultConstraint("DF_Student_DateOfBirth").Text = "'1800-00-00'"

有些额外的属性会根据你选择的数据类型显示出来。例如,[Microsoft.SqlServer. Management.SMO.DataType]::Int将允许你确认是否标识列并让你设置种子和增量。[Microsoft.SqlServer. Management.SMO.DataType]::Varchar 允许你设置长度。

一旦你设置了属性,你可以将列添加到表。

$table.Columns.Add($col4)

当一切都设置好后,你可以调用表的Create方法。

$table.Create()

现在,创建一个主键时,你需要创建另外两个SMO对象。第一个是Index对象。对于这个对象,你需要指定索引的类型,是聚集索引还是非聚集索引。

$PK = New-Object -TypeName Microsoft.SqlServer.Management.SMO. 
Index -ArgumentList $table, "PK_Student_StudentID"    
$PK.IsClustered = $true
$PK.IndexKeyType = [Microsoft.SqlServer.Management.SMO.
IndexKeyType]::DriPrimaryKey

第二个对象,IndexedColumn,指定索引中的列。

#identify columns part of the PK
$PKcol = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn 
-ArgumentList $PK, $col1Name

如果列为包含列,只需将IndexedColumn对象的IsIncluded属性设置为true。

一旦你创建了所有的索引列,你可以将它们添加到Index,并调用Index对象的Create方法:

$PK.IndexedColumns.Add($PKcol)
$PK.Create()

你可能会想我们刚才创建表的操作是一个非常长的方式。你想的没错,这个创建表的方式太繁琐。然而,注意,这只是另一种方式而已。如果你想创建表,并且T-SQL是一种更快的方法,那就用T-SQL。然而,知道如何使用PowerShell和SMO去完成工作可能只是多了一个工具,你可能需要动态创建表或更有扩展性—例如,你需要从多用户导入存储在Excel、CSV或XML文件中的定义。

2.12.4 可参阅…

创建索引方案
从MSDN检查完整的SMO DataType类的列表。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
10天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
51 10
|
10天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
68 6
|
4天前
|
SQL 数据库 HIVE
Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)
【4月更文挑战第8天】Hive【基础知识 05】常用DDL操作(数据库操作+创建表+修改表+清空删除表+其他命令)
13 0
|
5天前
|
SQL 安全 网络安全
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
IDEA DataGrip连接sqlserver 提示驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接的解决方法
9 0
|
6天前
|
SQL 数据库
数据库SQL语言实战(二)
数据库SQL语言实战(二)
|
6天前
|
SQL 关系型数据库 数据库
【后端面经】【数据库与MySQL】SQL优化:如何发现SQL中的问题?
【4月更文挑战第12天】数据库优化涉及硬件升级、操作系统调整、服务器/引擎优化和SQL优化。SQL优化目标是减少磁盘IO和内存/CPU消耗。`EXPLAIN`命令用于检查SQL执行计划,关注`type`、`possible_keys`、`key`、`rows`和`filtered`字段。设计索引时考虑外键、频繁出现在`where`、`order by`和关联查询中的列,以及区分度高的列。大数据表改结构需谨慎,可能需要停机、低峰期变更或新建表。面试中应准备SQL优化案例,如覆盖索引、优化`order by`、`count`和索引提示。优化分页查询时避免大偏移量,可利用上一批的最大ID进行限制。
32 3
|
9天前
|
SQL 监控 数据库
数据库管理与电脑监控软件:SQL代码优化与实践
本文探讨了如何优化数据库管理和使用电脑监控软件以提升效率。通过SQL代码优化,如使用索引和调整查询语句,能有效提高数据库性能。同时,合理设计数据库结构,如数据表划分和规范化,也能增强管理效率。此外,利用Python脚本自动化收集系统性能数据,并实时提交至网站,可实现对电脑监控的实时性和有效性。这些方法能提升信息系统稳定性和可靠性,满足用户需求。
37 0
|
10天前
|
SQL 存储 数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境: 一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5,划分LUN供这台服务器使用。 在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。 服务器故障&初检: 由于未知原因,Sql Server数据库文件丢失,丢失数据涉及到3个库,表的数量有3000左右。数据库文件丢失原因还没有查清楚,也不能确定数据存储位置。 数据库文件丢失后服务器仍处于开机状态,所幸没有大量数据写入。 将raid5中所有磁盘编号后取出,经过硬件工程师检测,没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像,镜像完成后将所
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
|
14天前
|
SQL 数据安全/隐私保护
SQL Server 2016安装教程
SQL Server 2016安装教程
17 1
|
14天前
|
SQL 安全 Java
SQL server 2017安装教程
SQL server 2017安装教程
15 1