在我公司的部署过程需要创造的程序和表格等SQL脚本是光秃秃的。
生成SQL Server Management Studio中创建脚本，让你比裸脚本，它是在这种情况下，不幸的显著更多。 例如，对于一个测试表这是对我产生SSMS：
USE [DemoDB]
GO
/****** Object: Table [dbo].[example] Script Date: 08/07/2012 15:46:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[example](
[fake_row] [int] NULL
) ON [PRIMARY]
GO
所以，我想知道的是：
我能以某种方式运行在SSMS SQL Server上的查询会给我一个剧本，这是真的只是下面？
create table example (
fake_row int
)
你可以创建自己的 “CREATE TABLE” 语句，使用INFORMATION_SCHEMA（亚伦我知道，INFORMATION_SCHEMA;。）。
我已经写了这个目的了一些代码，但它不使用INFORMATION_SCHEMA。 我知道，如果使用的是很可能计划要删除旧的系统表。 它还增加了一些额外的信息（你可以删除，因为你不需要它）。 只要把表的列表中@INCLUSIONLIST并在表所在的数据库运行此。
SET @INCLUSIONLIST = '|table1|table2|';
SELECT (CASE WHEN rownum = 1 THEN 'CREATE TABLE ['+a.t_name+'] ('
WHEN c.column_id IS NOT NULL
THEN ' ['+c.c_name+'] ' +
(CASE WHEN CHARINDEX('CHAR', datatype) > 0 THEN datatype+'('+(case when length < 0 then 'max' else cast(length as varchar) end)+')'
WHEN CHARINDEX('BINARY', datatype) > 0 THEN datatype+'('+cast(length as varchar)+')'
WHEN datatype = 'float' AND precision <> 24 THEN datatype+'('+cast(precision as varchar)+')'
WHEN datatype IN ('numeric', 'decimal') AND scale = 0 THEN datatype+'('+cast(precision as varchar)+')'
WHEN datatype IN ('numeric', 'decimal') AND scale > 0 THEN datatype+'('+cast(precision as varchar)+','+cast(scale as varchar)+')'
ELSE datatype END)+' '+
(CASE WHEN c.identity_seed IS NOT NULL
THEN 'IDENTITY(' + CAST(identity_seed AS VARCHAR) + ',' + CAST(identity_increment AS VARCHAR) + ') '
ELSE '' END) +
(CASE WHEN c.is_nullable = 0 THEN 'NOT NULL ' ELSE '' END) +
(CASE WHEN c.default_definition IS NOT NULL
THEN 'DEFAULT '+default_definition ELSE '' END) +
(CASE WHEN max_column_id = column_id AND pk.pk_name IS NULL THEN '' ELSE ',' END)
WHEN rownum = max_column_id + 2 and pk.pk_name IS NOT NULL
THEN ' PRIMARY KEY ('+pk.pk_columns+')'
WHEN rownum = max_column_id + 3 THEN ') /* CREATE TABLE '+a.t_name+' */'
WHEN rownum = max_column_id + 4 THEN 'GO'
WHEN rownum = max_column_id + 5 THEN ''
END)
FROM (SELECT t.t_name, rownum, max_column_id
FROM (SELECT t.name as t_name, MAX(c.column_id) as max_column_id
FROM sys.columns c join
(SELECT * FROM sys.tables WHERE CHARINDEX('|'+name+'|', @INCLUSIONLIST) > 0 ) t
ON c.object_id = t.object_id
GROUP BY t.name) t join
(SELECT ROW_NUMBER() OVER (ORDER BY object_id) as rownum FROM sys.columns c) ctr
ON ctr.rownum <= t.max_column_id + 5
) a LEFT OUTER JOIN
(SELECT t.name as t_name, c.column_id, c.name AS c_name, u.name as datatype,
ISNULL(baset.name, N'') AS systemtype,
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND c.max_length <> -1
THEN c.max_length/2 ELSE c.max_length END AS INT) AS length,
c.precision AS precision,
c.scale as scale,
c.is_nullable,
dc.definition as default_definition,
idc.seed_value as identity_seed, idc.increment_value as identity_increment
FROM sys.tables t JOIN
sys.all_columns AS c
ON c.object_id = t.object_id LEFT OUTER JOIN
sys.types u
ON u.user_type_id = c.user_type_id LEFT OUTER JOIN
sys.types baset
ON baset.user_type_id = c.system_type_id AND
baset.user_type_id = baset.system_type_id LEFT OUTER JOIN
sys.default_constraints dc
ON c.object_id = dc.parent_object_id AND
c.column_id = dc.parent_column_id LEFT OUTER JOIN
sys.identity_columns idc
ON c.object_id = idc.object_id AND
c.column_id = idc.column_id
) c
ON a.t_name = c.t_name AND
c.column_id + 1 = a.rownum LEFT OUTER JOIN
(SELECT t.name as t_name, kc.name as pk_name,
(MAX(CASE WHEN index_column_id = 1 THEN '['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 2 THEN ','+'['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 3 THEN ','+'['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 4 THEN ','+'['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 5 THEN ','+'['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 6 THEN ','+'['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 7 THEN ','+'['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 8 THEN ','+'['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 9 THEN ','+'['+c.name+']' ELSE '' END) +
MAX(CASE WHEN index_column_id = 10 THEN ','+'['+c.name+']' ELSE '' END)
) as pk_columns
FROM sys.indexes i JOIN
sys.key_constraints kc
ON i.name = kc.name AND
kc.type = 'PK' JOIN
sys.tables t
ON i.object_id = t.object_id JOIN
sys.index_columns ic
ON i.object_id = ic.object_id AND
i.index_id = ic.index_id JOIN
sys.columns c
ON ic.index_column_id = c.column_id AND
ic.object_id = c.object_id
GROUP BY t.name, kc.name
) pk
ON pk.t_name = a.t_name
ORDER BY a.t_name, rownum
很抱歉没能自我回答，标志着其他好的答案+1。
事实证明，你可以得到几乎所有的的方式出现在SSMS工具。
或者，你可以控制像这样SSMS的一般脚本行为： 为脚本的SQL Server数据库对象的选项 。
我想创建存储的特效从INFORMATION_SCHEMA视图读取。 获取存储过程文本只是：
DECLARE @SQL VARCHAR(8000)
SET @SQL=' '
select @SQL = @SQL + ROUTINE_DEFINITION
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'
AND SPECIFIC_NAME = 'updateComments'
PRINT @SQL
在一个脚本的表会看起来像：
DECLARE @tableName VARCHAR(50)
SET @tableName = 'Location'
-- Need to know how many columns
DECLARE @NumColumns int
SELECT @NumColumns = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
DECLARE @SQL VARCHAR(8000)
SET @SQL=' '
DECLARE @Results TABLE
(LineNumber int,
Line VARCHAR(1000))
INSERT INTO @Results
SELECT 0 AS ORDINAL_POSITION,
'CREATE TABLE '+TABLE_SCHEMA+'.'+@tableName+' (' AS Line
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tableName
UNION ALL
SELECT ORDINAL_POSITION,
'['+COLUMN_NAME+'] '+ DATA_TYPE +
CASE WHEN DATA_TYPE = 'varchar' OR DATA_TYPE = 'char'
THEN '('+ CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) +')'
ELSE '' END +
CASE WHEN IS_NULLABLE = 'NO'
THEN ' NULL '
ELSE ' NOT NULL' END +
CASE WHEN ORDINAL_POSITION < @NumColumns
THEN ', '
ELSE ' ' END
AS LINE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
UNION ALL
SELECT 999, ' )'
SELECT Line
FROM @Results
ORDER BY LineNumber
SELECT @SQL = @SQL + Line
FROM @Results
ORDER BY LineNumber
SELECT @SQL
PRINT @SQL
这将创建所需的简单的表脚本，但它是不完整的 - 没有做数值精度，主键等，但这也足以让你开始。
我只想让这些每一个成一个存储过程，是以对象名作为参数。
