在SQL Server中，如何为给定表生成CREATE TABLE语句？

本文介绍了在SQL Server中，如何为给定表生成CREATE TABLE语句？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我花了很多时间来解决这个问题，因此本着这篇文章，我将其发布在这里，因为我认为这可能对其他人有用。

I've spent a good amount of time coming up with solution to this problem, so in the spirit of this post, I'm posting it here, since I think it might be useful to others.

如果有人有更好的脚本或要添加的内容，请发布。

If anyone has a better script, or anything to add, please post it.

编辑：是的，我知道如何在Management Studio中进行操作-但我需要能够在另一个应用程序中进行操作。

Yes guys, I know how to do it in Management Studio - but I needed to be able to do it from within another application.

推荐答案

我已经修改了上面的版本，可以在所有表​​中运行并支持新的SQL 2005数据类型。它还保留主键名称。仅适用于SQL 2005（使用交叉应用）。

I've modified the version above to run for all tables and support new SQL 2005 data types. It also retains the primary key names. Works only on SQL 2005 (using cross apply).

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数据类型

Update: Added handling of the XML data type

更新2：解决了以下情况：1）有多个具有相同名称但模式不同的表，2）是具有相同名称的PK约束的多个表

Update 2: Fixed cases when 1) there is multiple tables with the same name but with different schemas, 2) there is multiple tables having PK constraint with the same name