在 SQLServer 中，如何为给定的表生成 CREATETABLE 语句？

我已经花了大量的时间想出了解决这个问题的办法，所以本着 这篇文章的精神，我把它贴在这里，因为我认为它可能对其他人有用。

如果有人有更好的脚本，或任何东西要添加，请发布它。

编辑: 是的，伙计们，我知道如何在 ManagementStudio 中做到这一点-但是我需要能够在另一个应用程序中做到这一点。

123484 次浏览
小开

这是我想出来的剧本。它处理 Identity 列、默认值和主键。它不处理外键、索引、触发器或任何其他聪明的东西。它适用于 SQLServer2000、2005和2008。

declare @schema varchar(100), @table varchar(100)
set @schema = 'dbo' -- set schema name here
set @table = 'MyTable' -- set table name here
declare @sql table(s varchar(1000), id int identity)


-- create statement
insert into  @sql(s) values ('create table [' + @table + '] (')


-- column list
insert into @sql(s)
select
'  ['+column_name+'] ' +
data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=@table
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(@table) as varchar) + ',' +
cast(ident_incr(@table) as varchar) + ')'
else ''
end + ' ' +
( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','


from INFORMATION_SCHEMA.COLUMNS where table_name = @table AND table_schema = @schema
order by ordinal_position


-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = @table and constraint_type='PRIMARY KEY'


if ( @pkname is not null ) begin
insert into @sql(s) values('  PRIMARY KEY (')
insert into @sql(s)
select '   ['+COLUMN_NAME+'],' from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where constraint_name = @pkname
order by ordinal_position
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values ('  )')
end
else begin
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
end


-- closing bracket
insert into @sql(s) values( ')' )


-- result!
select s from @sql order by id
小开

感谢@博客胡子分享他的剧本。我一定会把它收藏起来，以备不时之需。

是的，你可以在表格上“右键点击”然后编写 CREATE TABLE脚本，但是:

  • 该脚本将包含 很多的 cruft (有人对扩展属性感兴趣吗?)
  • 如果您的模式中有200多个表，那么手工编写这些表将花费您半天的时间。

通过这个脚本转换成一个存储过程，并结合一个包装器脚本，您将有一个很好的自动化方式来转储您的表设计到源代码控制等。

其余的数据库代码(SP、 FK 索引、触发器等)无论如何都在源代码控制之下;)

小开

我注意到了一些东西——在 INFORMATION _ SCHEMA 中。COLUMNS 视图中，CHARACTER _ MAXIMUM _ LENGTH 为图像和文本等字段类型提供的大小为2147483647(2 ^ 31-1)。Ntext 是2 ^ 30-1(双字节 Unicode)。

此大小包含在此查询的输出中，但在 CREATE 语句中对这些数据类型无效(它们根本不应该具有最大大小值)。因此，除非手动更正这些结果，否则 CREATE 脚本将无法在给定这些数据类型的情况下工作。

我想可以修复脚本来解决这个问题，但这超出了我的 SQL 能力。

小开

如果您正在使用管理工作室，并有查询分析器窗口打开，您可以拖动表名称的查询分析器窗口和... 宾果！你拿到桌子脚本。 我在 SQL2008中没有尝试过这种方法

小开
最佳答案

我已经修改了上面的版本，可以运行所有表并支持新的 SQL2005数据类型。它还保留主键名。只能在 SQL2005上工作(使用交叉应用)。


select  'create table [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END
from    sysobjects so
cross apply
(SELECT
'  ['+column_name+'] ' +
data_type + case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'ntext' then ''
when 'xml' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(so.name) as varchar) + ',' +
cast(ident_incr(so.name) as varchar) + ')'
else ''
end + ' ' +
(case when UPPER(IS_NULLABLE) = 'NO' then 'NOT ' else '' end ) + 'NULL ' +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '


from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH('')) o (list)
left join
information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM   information_schema.key_column_usage kcu
WHERE  kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')

更新: 添加了对 XML 数据类型的处理

更新2: 修正了以下情况: 1)有多个具有相同名称但具有不同模式的表; 2)有多个具有相同名称的 PK 约束的表

小开

如果从中生成脚本的应用程序是。NET 应用程序，您可能需要研究如何使用 SMO (Sql 管理对象)。关于如何使用 SMO 脚本对象，请参考此 SQL 团队链接。

小开

--或者您可以创建一个存储过程... ... 首先使用 Id 创建

USE [db]
GO


/****** Object:  StoredProcedure [dbo].[procUtils_InsertGeneratorWithId]    Script Date: 06/13/2009 22:18:11 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO




create PROC [dbo].[procUtils_InsertGeneratorWithId]
(
@domain_user varchar(50),
@tableName varchar(100)
)




as


--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
DECLARE @IDENTITY_STRING nvarchar ( 100 )
SET @IDENTITY_STRING = ' '
select  @IDENTITY_STRING
SET @string='INSERT '+@tableName+'('
SET @stringData=''


DECLARE @colName nvarchar(50)


FETCH NEXT FROM cursCol INTO @colName,@dataType


IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END


WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
--                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END


SET @string=@string+@colName+','


FETCH NEXT FROM cursCol INTO @colName,@dataType
END
DECLARE @Query nvarchar(4000)


SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
--select @query


CLOSE cursCol
DEALLOCATE cursCol




/*
USAGE


*/


GO

--第二个没有 ID 插入

USE [db]
GO


/****** Object:  StoredProcedure [dbo].[procUtils_InsertGenerator]    Script Date: 06/13/2009 22:20:52 ******/
SET ANSI_NULLS ON
GO


SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[procUtils_InsertGenerator]
(
@domain_user varchar(50),
@tableName varchar(100)
)




as


--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR




-- SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName
/* NEW
SELECT c.name , sc.data_type  FROM sys.extended_properties AS ep
INNER JOIN sys.tables AS t ON ep.major_id = t.object_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id
= c.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS sc ON t.name = sc.table_name and
c.name = sc.column_name
WHERE t.name = @tableName and c.is_identity=0
*/


select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"
from sys.columns c
join sys.systypes s on (s.xtype = c.system_type_id)
where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')
AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName  ) and c.is_identity=0 and s.name not like 'sysname'








OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
DECLARE @IDENTITY_STRING nvarchar ( 100 )
SET @IDENTITY_STRING = ' '
select  @IDENTITY_STRING
SET @string='INSERT '+@tableName+'('
SET @stringData=''


DECLARE @colName nvarchar(50)


FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType


IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END


WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
--                             'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END


SET @string=@string+@colName+','


FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType
END
DECLARE @Query nvarchar(4000)


SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
--select @query


CLOSE cursCol
DEALLOCATE cursCol




/*


use poc
go


DECLARE @RC int
DECLARE @domain_user varchar(50)
DECLARE @tableName varchar(100)


-- TODO: Set parameter values here.
set @domain_user='yorgeorg'
set @tableName = 'tbGui_WizardTabButtonAreas'


EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]
@domain_user
,@tableName


*/
GO
小开

在经典的 asp 中显示 create 表(处理约束、主键、复制表结构和/或数据...)

显示创建表 来自 Microsoft SQL Server 的“显示创建表”和“显示创建数据库”命令。 这个脚本是用 Microsoft asp 语言编写的，很容易移植到另一种语言。 *

小开

MSDB论坛中有一个 Powershell 脚本，它将为所有表和相关对象编写脚本:

# Script all tables in a database
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
| out-null


$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') '<Servername>'
$db = $s.Databases['<Database>']


$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)
$scrp.Options.AppendToFile = $True
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $False
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.WithDependencies = $True
$scrp.Options.FileName = 'C:\Temp\<Database>.SQL'


foreach($item in $db.Tables) { $tablearray+=@($item) }
$scrp.Script($tablearray)


Write-Host "Scripting complete"
小开

对模式的支持:

这是一个更新的版本，修正了大卫等人的伟大答案。新增的是对命名架构的支持。应该注意的是，如果在不同的模式中实际存在相同名称的表，这可能会中断。另一个改进是使用了官方的 QuoteName ()函数。

SELECT
t.TABLE_CATALOG,
t.TABLE_SCHEMA,
t.TABLE_NAME,
'create table '+QuoteName(t.TABLE_SCHEMA)+'.' + QuoteName(so.name) + ' (' + LEFT(o.List, Len(o.List)-1) + ');  '
+ CASE WHEN tc.Constraint_Name IS NULL THEN ''
ELSE
'ALTER TABLE ' + QuoteName(t.TABLE_SCHEMA)+'.' + QuoteName(so.name)
+ ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ');  '
END as 'SQL_CREATE_TABLE'
FROM sysobjects so


CROSS APPLY (
SELECT
'  ['+column_name+'] '
+  data_type
+ case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'ntext' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else
coalesce(
'('+ case when character_maximum_length = -1
then 'MAX'
else cast(character_maximum_length as varchar) end
+ ')','')
end
+ ' '
+ case when exists (
SELECT id
FROM syscolumns
WHERE
object_name(id) = so.name
and name = column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(so.name) as varchar) + ',' +
cast(ident_incr(so.name) as varchar) + ')'
else ''
end
+ ' '
+ (case when IS_NULLABLE = 'No' then 'NOT ' else '' end)
+ 'NULL '
+ case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT
ELSE ''
END
+ ','  -- can't have a field name or we'll end up with XML


FROM information_schema.columns
WHERE table_name = so.name
ORDER BY ordinal_position
FOR XML PATH('')
) o (list)


LEFT JOIN information_schema.table_constraints tc on
tc.Table_name = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'


LEFT JOIN information_schema.tables t on
t.Table_name = so.Name


CROSS APPLY (
SELECT QuoteName(Column_Name) + ', '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
) j (list)


WHERE
xtype = 'U'
AND name NOT IN ('dtproperties')
-- AND so.name = 'ASPStateTempSessions'
;

..

在 ManagementStudio 中使用:

上面的 sql 代码的一个缺点是，如果您使用 SSMS 测试它，那么长语句不容易阅读。因此，根据 这篇有用的文章，这里有另一个版本，在点击网格中单元格的链接后，稍微修改了一下，使眼睛看起来更方便。对于 db 中的每个表，结果更容易识别为格式良好的 CREATETABLE 语句。

-- settings
DECLARE @CRLF NCHAR(2)
SET @CRLF = Nchar(13) + NChar(10)
DECLARE @PLACEHOLDER NCHAR(3)
SET @PLACEHOLDER = '{:}'


-- the main query
SELECT
t.TABLE_CATALOG,
t.TABLE_SCHEMA,
t.TABLE_NAME,
CAST(
REPLACE(
'create table ' + QuoteName(t.TABLE_SCHEMA) + '.' + QuoteName(so.name) + ' (' + @CRLF
+ LEFT(o.List, Len(o.List) - (LEN(@PLACEHOLDER)+2)) + @CRLF + ');' + @CRLF
+ CASE WHEN tc.Constraint_Name IS NULL THEN ''
ELSE
'ALTER TABLE ' + QuoteName(t.TABLE_SCHEMA) + '.' + QuoteName(so.Name)
+ ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY (' + LEFT(j.List, Len(j.List) - 1) + ');' + @CRLF
END,
@PLACEHOLDER,
@CRLF
)
AS XML) as 'SQL_CREATE_TABLE'
FROM sysobjects so


CROSS APPLY (
SELECT
'   '
+ '['+column_name+'] '
+  data_type
+ case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'ntext' then ''
when 'decimal' then '(' + cast(numeric_precision as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else
coalesce(
'('+ case when character_maximum_length = -1
then 'MAX'
else cast(character_maximum_length as varchar) end
+ ')','')
end
+ ' '
+ case when exists (
SELECT id
FROM syscolumns
WHERE
object_name(id) = so.name
and name = column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(so.name) as varchar) + ',' +
cast(ident_incr(so.name) as varchar) + ')'
else ''
end
+ ' '
+ (case when IS_NULLABLE = 'No' then 'NOT ' else '' end)
+ 'NULL '
+ case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT
ELSE ''
END
+ ', '
+ @PLACEHOLDER  -- note, can't have a field name or we'll end up with XML


FROM information_schema.columns where table_name = so.name
ORDER BY ordinal_position
FOR XML PATH('')
) o (list)


LEFT JOIN information_schema.table_constraints tc on
tc.Table_name = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'


LEFT JOIN information_schema.tables t on
t.Table_name = so.Name


CROSS APPLY (
SELECT QUOTENAME(Column_Name) + ', '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY ORDINAL_POSITION
FOR XML PATH('')
) j (list)


WHERE
xtype = 'U'
AND name NOT IN ('dtproperties')
-- AND so.name = 'ASPStateTempSessions'
;

不是我多嘴，下面是功能上等价的例子输出，以供比较:

-- 1 (scripting version)
create table [dbo].[ASPStateTempApplications] (  [AppId] int  NOT NULL ,  [AppName] char(280)  NOT NULL );  ALTER TABLE [dbo].[ASPStateTempApplications] ADD CONSTRAINT PK__ASPState__8E2CF7F908EA5793 PRIMARY KEY  ([AppId]);


-- 2 (SSMS version)
create table [dbo].[ASPStateTempSessions] (
[SessionId] nvarchar(88)  NOT NULL ,
[Created] datetime  NOT NULL DEFAULT (getutcdate()),
[Expires] datetime  NOT NULL ,
[LockDate] datetime  NOT NULL ,
[LockDateLocal] datetime  NOT NULL ,
[LockCookie] int  NOT NULL ,
[Timeout] int  NOT NULL ,
[Locked] bit  NOT NULL ,
[SessionItemShort] varbinary(7000)  NULL ,
[SessionItemLong] image(2147483647)  NULL ,
[Flags] int  NOT NULL DEFAULT ((0))
);
ALTER TABLE [dbo].[ASPStateTempSessions] ADD CONSTRAINT PK__ASPState__C9F4929003317E3D PRIMARY KEY ([SessionId]);

..

不利因素:

应该指出的是，由于缺乏对主键以外的其他索引的支持，我对此仍然比较不满意。它仍然适合用作简单数据导出或复制的机制。

小开

还有一个支持外键的变体，在一个语句中:

 SELECT
obj.name
,'CREATE TABLE [' + obj.name + '] (' + LEFT(cols.list, LEN(cols.list) - 1 ) + ')'
+ ISNULL(' ' + refs.list, '')
FROM sysobjects obj
CROSS APPLY (
SELECT
CHAR(10)
+ ' [' + column_name + '] '
+ data_type
+ CASE data_type
WHEN 'sql_variant' THEN ''
WHEN 'text' THEN ''
WHEN 'ntext' THEN ''
WHEN 'xml' THEN ''
WHEN 'decimal' THEN '(' + CAST(numeric_precision as VARCHAR) + ', ' + CAST(numeric_scale as VARCHAR) + ')'
ELSE COALESCE('(' + CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length as VARCHAR) END + ')', '')
END
+ ' '
+ case when exists ( -- Identity skip
select id from syscolumns
where object_name(id) = obj.name
and name = column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(obj.name) as varchar) + ',' +
cast(ident_incr(obj.name) as varchar) + ')'
else ''
end + ' '
+ CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END
+ 'NULL'
+ CASE WHEN information_schema.columns.column_default IS NOT NULL THEN ' DEFAULT ' + information_schema.columns.column_default ELSE '' END
+ ','
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE table_name = obj.name
ORDER BY ordinal_position
FOR XML PATH('')
) cols (list)
CROSS APPLY(
SELECT
CHAR(10) + 'ALTER TABLE ' + obj.name + '_noident_temp ADD ' + LEFT(alt, LEN(alt)-1)
FROM(
SELECT
CHAR(10)
+ ' CONSTRAINT ' + tc.constraint_name
+ ' ' + tc.constraint_type + ' (' + LEFT(c.list, LEN(c.list)-1) + ')'
+ COALESCE(CHAR(10) + r.list, ', ')
FROM
information_schema.table_constraints tc
CROSS APPLY(
SELECT
'[' + kcu.column_name + '], '
FROM
information_schema.key_column_usage kcu
WHERE
kcu.constraint_name = tc.constraint_name
ORDER BY
kcu.ordinal_position
FOR XML PATH('')
) c (list)
OUTER APPLY(
-- // http://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema
SELECT
'  REFERENCES [' + kcu1.constraint_schema + '].' + '[' + kcu2.table_name + ']' + '(' + kcu2.column_name + '), '
FROM information_schema.referential_constraints as rc
JOIN information_schema.key_column_usage as kcu1 ON (kcu1.constraint_catalog = rc.constraint_catalog AND kcu1.constraint_schema = rc.constraint_schema AND kcu1.constraint_name = rc.constraint_name)
JOIN information_schema.key_column_usage as kcu2 ON (kcu2.constraint_catalog = rc.unique_constraint_catalog AND kcu2.constraint_schema = rc.unique_constraint_schema AND kcu2.constraint_name = rc.unique_constraint_name AND kcu2.ordinal_position = KCU1.ordinal_position)
WHERE
kcu1.constraint_catalog = tc.constraint_catalog AND kcu1.constraint_schema = tc.constraint_schema AND kcu1.constraint_name = tc.constraint_name
) r (list)
WHERE tc.table_name = obj.name
FOR XML PATH('')
) a (alt)
) refs (list)
WHERE
xtype = 'U'
AND name NOT IN ('dtproperties')
AND obj.name = 'your_table_name'

你可以试试是 sqlfiddle: http://sqlfiddle.com/#!6/e3b66/3/0

小开

我修改了接受的答案，现在它可以得到命令，包括主键和外键在一定的模式。

declare @table varchar(100)
declare @schema varchar(100)
set @table = 'Persons' -- set table name here
set @schema = 'OT' -- set SCHEMA name here
declare @sql table(s varchar(1000), id int identity)


-- create statement
insert into  @sql(s) values ('create table ' + @table + ' (')


-- column list
insert into @sql(s)
select
'  '+column_name+' ' +
data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=@table
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(@table) as varchar) + ',' +
cast(ident_incr(@table) as varchar) + ')'
else ''
end + ' ' +
( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','


from information_schema.columns where table_name = @table and table_schema = @schema
order by ordinal_position


-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='PRIMARY KEY'


if ( @pkname is not null ) begin
insert into @sql(s) values('  PRIMARY KEY (')
insert into @sql(s)
select '   '+COLUMN_NAME+',' from information_schema.key_column_usage
where constraint_name = @pkname
order by ordinal_position
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values ('  )')
end
else begin
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
end




-- foreign key
declare @fkname varchar(100)
select @fkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='FOREIGN KEY'


if ( @fkname is not null ) begin
insert into @sql(s) values(',')
insert into @sql(s) values('  FOREIGN KEY (')
insert into @sql(s)
select '   '+COLUMN_NAME+',' from information_schema.key_column_usage
where constraint_name = @fkname
order by ordinal_position
-- remove trailing comma
update @sql set s=left(s,len(s)-1) where id=@@identity
insert into @sql(s) values ('  ) REFERENCES ')
insert into @sql(s)
SELECT
OBJECT_NAME(fk.referenced_object_id)
FROM
sys.foreign_keys fk
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
INNER JOIN
sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
where fk.name = @fkname
insert into @sql(s)
SELECT
'('+c2.name+')'
FROM
sys.foreign_keys fk
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
INNER JOIN
sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
where fk.name = @fkname
end


-- closing bracket
insert into @sql(s) values( ')' )


-- result!
select s from @sql order by id
小开

我将通过支持分区表来改进这个问题的答案:

使用下面的脚本查找分区方案和分区密钥:

declare @partition_scheme varchar(100) = (
select distinct ps.Name AS PartitionScheme
from sys.indexes i
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
where i.object_id = object_id('your table name')
)
print @partition_scheme


declare @partition_column varchar(100) = (
select c.name
from  sys.tables          t
join  sys.indexes         i
on(i.object_id = t.object_id
and i.index_id < 2)
join  sys.index_columns  ic
on(ic.partition_ordinal > 0
and ic.index_id = i.index_id and ic.object_id = t.object_id)
join  sys.columns         c
on(c.object_id = ic.object_id
and c.column_id = ic.column_id)
where t.object_id  = object_id('your table name')
)
print @partition_column

然后修改生成查询，在正确的位置添加以下代码行:

+ IIF(@partition_scheme is null, '', 'ON [' + @partition_scheme + ']([' + @partition_column + '])')
小开

包括计算列的定义

    select 'CREATE TABLE [' + so.name + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + so.Name + ' ADD CONSTRAINT ' + tc.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')' END, name
from    sysobjects so
cross apply
(SELECT


case when comps.definition is not null then '  ['+column_name+'] AS ' + comps.definition
else
'  ['+column_name+'] ' + data_type +
case
when data_type like '%text' or data_type in ('image', 'sql_variant' ,'xml')
then ''
when data_type in ('float')
then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ')'
when data_type in ('datetime2', 'datetimeoffset', 'time')
then '(' + cast(coalesce(datetime_precision, 7) as varchar(11)) + ')'
when data_type in ('decimal', 'numeric')
then '(' + cast(coalesce(numeric_precision, 18) as varchar(11)) + ',' + cast(coalesce(numeric_scale, 0) as varchar(11)) + ')'
when (data_type like '%binary' or data_type like '%char') and character_maximum_length = -1
then '(max)'
when character_maximum_length is not null
then '(' + cast(character_maximum_length as varchar(11)) + ')'
else ''
end + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(so.name) as varchar) + ',' +
cast(ident_incr(so.name) as varchar) + ')'
else ''
end + ' ' +
(case when information_schema.columns.IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END
end + ', '


from information_schema.columns
left join sys.computed_columns comps
on OBJECT_ID(information_schema.columns.TABLE_NAME)=comps.object_id and information_schema.columns.COLUMN_NAME=comps.name


where table_name = so.name
order by ordinal_position
FOR XML PATH('')) o (list)
left join
information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM   information_schema.key_column_usage kcu
WHERE  kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
小开

我意识到已经过去很长时间了，但我还是想补充一下。如果只需要表，而不需要可以使用的 create table 语句

select into x from db.schema.y where 1=0

将表复制到新的数据库

小开

基于 Hubbitus 回答的查询。

  • 包括架构名称
  • 修复具有多个字段的外键
  • 包括级联更新和删除
  • 包括一个有条件的丢弃表
SELECT
Schema_Name = SCHEMA_NAME(obj.uid)
, Table_Name = name
, Drop_Table = 'IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA = ''' + SCHEMA_NAME(obj.uid) + '''  AND  TABLE_NAME = ''' + obj.name + '''))
DROP TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] '
, Create_Table ='
CREATE TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] (' + LEFT(cols.list, LEN(cols.list) - 1 ) + ')' + ISNULL(' ' + refs.list, '')
FROM sysobjects obj
CROSS APPLY (
SELECT
CHAR(10)
+ ' [' + column_name + '] '
+ data_type
+ CASE data_type
WHEN 'sql_variant' THEN ''
WHEN 'text' THEN ''
WHEN 'ntext' THEN ''
WHEN 'xml' THEN ''
WHEN 'decimal' THEN '(' + CAST(numeric_precision as VARCHAR) + ', ' + CAST(numeric_scale as VARCHAR) + ')'
ELSE COALESCE('(' + CASE WHEN character_maximum_length = -1 THEN 'MAX' ELSE CAST(character_maximum_length as VARCHAR) END + ')', '')
END
+ ' '
+ case when exists ( -- Identity skip
select id from syscolumns
where id = obj.id
and name = column_name
and columnproperty(id, name, 'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(obj.name) as varchar) + ',' +
cast(ident_incr(obj.name) as varchar) + ')'
else ''
end + ' '
+ CASE WHEN IS_NULLABLE = 'No' THEN 'NOT ' ELSE '' END
+ 'NULL'
+ CASE WHEN IC.column_default IS NOT NULL THEN ' DEFAULT ' + IC.column_default ELSE '' END
+ ','
FROM INFORMATION_SCHEMA.COLUMNS IC
WHERE IC.table_name   = obj.name
AND IC.TABLE_SCHEMA = SCHEMA_NAME(obj.uid)
ORDER BY ordinal_position
FOR XML PATH('')
) cols (list)
CROSS APPLY(
SELECT
CHAR(10) + 'ALTER TABLE [' + SCHEMA_NAME(obj.uid) + '].[' + obj.name + '] ADD ' + LEFT(alt, LEN(alt)-1)
FROM(
SELECT
CHAR(10)
+ ' CONSTRAINT ' + tc.constraint_name
+ ' ' + tc.constraint_type + ' (' + LEFT(c.list, LEN(c.list)-1) + ')'
+ COALESCE(CHAR(10) + r.list, ', ')
FROM information_schema.table_constraints tc
CROSS APPLY(
SELECT   '[' + kcu.column_name + '], '
FROM     information_schema.key_column_usage kcu
WHERE    kcu.constraint_name = tc.constraint_name
ORDER BY kcu.ordinal_position
FOR XML PATH('')
) c (list)
OUTER APPLY(
-- // http://stackoverflow.com/questions/3907879/sql-server-howto-get-foreign-key-reference-from-information-schema
SELECT LEFT(f.list, LEN(f.list)-1) + ')' + IIF(rc.DELETE_RULE = 'NO ACTION', '', ' ON DELETE ' + rc.DELETE_RULE) + IIF(rc.UPDATE_RULE = 'NO ACTION', '', ' ON UPDATE ' + rc.UPDATE_RULE) + ', '
FROM information_schema.referential_constraints rc
CROSS APPLY(
SELECT IIF(kcu.ordinal_position = 1, ' REFERENCES [' + kcu.table_schema + '].[' + kcu.table_name + '] (', '')
+ '[' + kcu.column_name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.constraint_catalog = rc.unique_constraint_catalog AND kcu.constraint_schema = rc.unique_constraint_schema AND kcu.constraint_name = rc.unique_constraint_name
ORDER BY kcu.ordinal_position
FOR XML PATH('')
) f (list)
WHERE rc.constraint_catalog = tc.constraint_catalog
AND rc.constraint_schema  = tc.constraint_schema
AND rc.constraint_name    = tc.constraint_name
) r (list)
WHERE tc.table_name = obj.name
FOR XML PATH('')
) a (alt)
) refs (list)
WHERE xtype = 'U'

将拖放表(如果存在)与创建使用结合起来，如下所示:

SELECT Drop_Table + CHAR(10) + Create_Table FROM SysCreateTables

提交答案