在 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