在SQL Server中，如何为给定表生成CREATE TABLE语句？ [英] In SQL Server, how do I generate a CREATE TABLE statement for a given 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

这篇关于在SQL Server中，如何为给定表生成CREATE TABLE语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！