要感谢在Microsoft的贡献者们在SQL Server管理套件中提供了一个设置参数选项用于脚本输出的方法了。在Tools\Options下面有一个用于改变与管理套件各个方面相关的各种选项对话框表单。在这个表单的左边面板中选择Scripting将显示你可以用来使你的脚本更加适合于你的各种格式化设置，Microsoft开发人员正是为所有数据库专业人员考虑的。
图1
图2
USE [Northwind]
GO
/****** Object: table [dbo].[Categories] Script Date: 05/18/2008 21:38:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE table [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](15) NOT NULL,
[Description] [ntext] NULL,
[Picture] [image] NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
现在让我们看看一些更重要的脚本修改选项对这个脚本所起的作用。
包括描述性的Header： 正如你所期望的，这个选项决定在你的脚本中描述性的header是ON还是OFF。
USE [Northwind]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE table [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](15) NOT NULL,
[Description] [ntext] NULL,
[Picture] [image] NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
包括IF NOT EXISTS条件从句： 这个设置表示是否在创建一个对象之前检查它是否存在。
USE [Northwind]
GO
/****** Object: table [dbo].[Categories] Script Date: 05/18/2008 21:55:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[Categories]') AND type IN (N'U'))
BEGIN
CREATE table [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](15) NOT NULL,
[Description] [ntext] NULL,
[Picture] [image] NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
脚本权限：将产生要生成的这个或这些对象所关联的数据库用户和角色权限。
USE [Northwind]
GO
/****** Object: table [dbo].[Categories] Script Date: 05/18/2008 22:04:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE table [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](15) NOT NULL,
[Description] [ntext] NULL,
[Picture] [image] NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
GRANT DELETE ON [dbo].[Categories] TO [public]
GO
GRANT INSERT ON [dbo].[Categories] TO [public]
GO
GRANT REFERENCES ON [dbo].[Categories] TO [public]
GO
GRANT SELECT ON [dbo].[Categories] TO [public]
GO
GRANT UPDATE ON [dbo].[Categories] TO [public]
脚本索引： 将为要生成的表生成任何关联的索引。
USE [Northwind]
GO
/****** Object: table [dbo].[Categories] Script Date: 05/18/2008 22:11:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE table [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [nvarchar](15) NOT NULL,
[Description] [ntext] NULL,
[Picture] [image] NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Index [CategoryName] Script Date: 05/18/2008 22:11:32 ******/
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]
使用SQL Server管理套件的脚本选项定制工具使你能够让你的脚本如你所需的详细或改进。脚本生成是最适合你的环境的。
本文作者:佚名 来源:本站原创
CIO之家 www.ciozj.com 微信公众号:imciow