生成建表脚本up_CreateTable

 已经很久没用使用这个脚本了,今天用到,并做修改,增加了生成扩展属性功能。

 

Go
if object_ID('[up_CreateTable]') is not null
    Drop Procedure [up_CreateTable]
Go
/* 生成建表脚本(V4.0)  Andy 2017-3-28 */
Create Proc up_CreateTable
(
    @objectList nvarchar(max)=null
)
as
--With ENCRYPTION
  
/*  参数说明:
    @objectList 对象列表,对象之间使用","隔开
     
    存储过程生成的建表脚本,包含Column,Constraint,Index,extended_properties
  
    Modify: andy 2017-3-28 增加了扩展属性
*/
Set Nocount On
    Declare @sql nvarchar(max),
            @objectid int,
            @id int,
            @Rowcount int,
            @ObjectName sysname,
            @Enter nvarchar(2),
            @Tab nvarchar(2)
  
    Select     @Enter=Char(13)+Char(10),
            @Tab=Char(9)   
  
    Declare @Tmp Table(name sysname)
     
    If @objectList>''
    Begin
        Set @sql='Select N'''+Replace(@objectList,',',''' Union All Select N''')+''''
        Insert Into @Tmp (name) Exec(@sql)
  
        Set @sql=null
        Select @sql=Isnull(@sql+',','')+name
            From @Tmp As a
            Where Not Exists(Select 1 From sys.objects Where type='U' And name=a.name)
  
        If @sql>''
        Begin
            Set @sql='发现无效的表名: '+@sql
           Raiserror (50001,-1,-1, @sql)
            Return(1)
         
        End
    End
  
    If object_id('tempdb..#Objects') Is Not Null
        Drop Table #Objects
     
    If object_id('tempdb..#Columns') Is Not Null
        Drop Table #Columns   
     
    Create Table #Objects(id int Identity(1,1) Primary Key,object_id int,name sysname)
  
      
    ;With t As
    (
    Select Object_id,Convert(int,0) As LevelNo,name As object_name
            From sys.objects a
            Where Type='U' And is_ms_shipped=0 And Not Exists(Select 1 From sys.foreign_keys Where referenced_object_id=a.object_id)
    Union All
    Select a.referenced_object_id As Object_id,b.LevelNo+1 As LevelNo,c.name As object_name
        From sys.foreign_keys a
            Inner Join t b On b.object_id=a.parent_object_id
            Inner Join sys.objects c On c.object_id=a.referenced_object_id And c.is_ms_shipped=0
        where a.referenced_object_id<>a.parent_object_id
    )
    Insert Into #Objects(object_id,name)
        Select a.object_id,object_name
            From t a
            Where    Not Exists(Select 1 From t Where object_id=a.object_id And LevelNo>a.LevelNo) And
                    Not Exists(Select 1 From sys.extended_properties Where major_id=a.object_id And minor_id=0 And class=1 And Name=N'microsoft_database_tools_support')
                    And (Exists(Select 1 From @Tmp Where name=a.object_name) Or Not Exists(Select 1 From @Tmp))
            Group By object_id,object_name,LevelNo
            Order By LevelNo Desc
  
    Set @Rowcount=@@Rowcount
    If @Rowcount=0
    Begin
       -- Raiserror 50001 N'没有可以生产脚本的表!'
        Raiserror (50001,-1,-1, N'没有可以生产脚本的表!')
        Return(1)
    End
  
    --Column
    Select    a.object_id,
            a.column_id As Seq,
            Cast(1 As tinyint) As DefinitionType,
            Quotename(a.name)+Char(32)+ c.name +
            Case
                When a.user_type_id In (231,239) Then '('+Case a.max_length When -1 Then 'Max' Else Rtrim(a.max_length/2) End +')'
                When a.user_type_id In (62,165,167,173,175) Then '('+Case a.max_length When -1 Then 'Max' Else Rtrim(a.max_length) End+')'
                When a.user_type_id In (106,108) Then '('+Rtrim(a.[precision])+','+Rtrim(a.scale)+')'
                Else ''
            End
            + Char(32)+
            Case a.is_rowguidcol When 1 Then 'Rowguidcol ' Else '' End +
            Case a.is_identity When 1 Then 'Identity('+Cast(d.seed_value As nvarchar(10))+','+Cast(d.increment_value As nvarchar(10))+') ' Else '' End+
            Case a.is_nullable When 1 Then 'Null ' Else 'Not Null ' End+
            Isnull('Constraint '+Quotename(e.name)+' Default('+e.definition+')','') As definition
  
            Into #Columns
        From sys.columns As a
            Inner Join #Objects As b On b.object_id=a.object_id
            Inner Join sys.types As c On c.user_type_id=a.user_type_id
            Left Outer Join sys.identity_columns As d On d.object_id=a.object_id And d.column_id=a.column_id And a.is_identity=1
            Left Outer Join sys.Default_constraints As e On e.object_id=a.default_object_id And e.parent_column_id=a.column_id
  
        Create Nonclustered Index IX_#Columns_object_id On #Columns(object_id Asc)
  
        --Constraint
        Insert Into #Columns
     
        Select    a.parent_object_id As object_id,
                Row_number() Over(Partition By a.parent_object_id Order By Case a.type When 'PK' Then 1 When 'C' Then 2 Else 3 End)As Seq,
                2 As DefinitionType,
                'Alter Table '+Quotename(object_name(a.parent_object_id)) +' Add Constraint '+Quotename(a.name)+
                Case a.type
                    When 'PK' Then ' Primary Key '+Case When Exists(Select 1 From sys.indexes Where object_id=a.parent_object_id And is_primary_key=1 And type=1) Then N'Clustered ' Else N'Nonclustered ' End+
                                                '('+Stuff((Select ','+Quotename(c1.Name)+Case a1.is_descending_key When 1 Then ' Desc' Else ' Asc' End
                                                        From sys.index_columns As a1
                                                            Inner Join sys.indexes As b1 On b1.object_id=a1.object_id And b1.index_id=a1.index_id And b1.is_primary_key=1
                                                            Inner Join sys.columns As c1 On c1.object_id=a1.object_id And c1.column_id=a1.column_id
                                                        Where a1.object_id=a.parent_object_id
                                                        For Xml Path('')
                                                    ),1,1,'')+
                                                ')'
                    When 'F' Then ' Foreign Key ('+Stuff((Select ','+Quotename(b1.Name)
                                                        From sys.foreign_key_columns As a1
                                                            Inner Join sys.columns As b1 On b1.object_id=a1.parent_object_id And b1.column_id=a1.parent_column_id
                                                        Where a1.constraint_object_id=a.object_id
                                                        Order By a1.constraint_column_id
                                                        For Xml Path('')
                                                    ),1,1,'')+
                                                ') References '+(Select Quotename(object_name(referenced_object_id)) From  sys.foreign_keys Where object_id=a.object_id)+
                                                ' ('
                                                    +Stuff((Select ','+Quotename(b1.Name)
                                                        From sys.foreign_key_columns As a1
                                                            Inner Join sys.columns As b1 On b1.object_id=a1.referenced_object_id And b1.column_id=a1.referenced_column_id
                                                        Where a1.constraint_object_id=a.object_id
                                                        Order By a1.constraint_column_id
                                                        For Xml Path('')
                                                    ),1,1,'')+
                                                ')'
                    When 'UQ' Then ' Unique'+(Select Case a1.type When 1 Then ' Clustered' Else ' Nonclustered' End
                                                    From sys.indexes As a1
                                                    Where a1.object_id=a.parent_object_id
                                                                And Exists(Select 1 From sys.key_constraints Where object_id=a.object_id And parent_object_id=a1.object_id And unique_index_id=a1.index_id)
                                               )+                       
                                                '('+Stuff((Select ','+Quotename(c1.Name)+Case a1.is_descending_key When 1 Then ' Desc' Else ' Asc' End
                                                        From sys.index_columns As a1
                                                            Inner Join sys.indexes As b1 On b1.object_id=a1.object_id And b1.index_id=a1.index_id And b1.is_unique_constraint=1
                                                            Inner Join sys.columns As c1 On c1.object_id=a1.object_id And c1.column_id=a1.column_id
                                                        Where a1.object_id=a.parent_object_id
                                                                And Exists(Select 1 From sys.key_constraints Where object_id=a.object_id And parent_object_id=a1.object_id And unique_index_id=a1.index_id)
                                                        For Xml Path('')
                                                    ),1,1,'')+
                                                ')'
                    When 'C' Then ' Check' +(Select definition From sys.check_constraints Where object_id=a.object_id)
                    Else ''
                End As definition
  
            From sys.objects As a
            Where a.type In('PK','F','C','UQ')
                    And Exists(Select From #Objects Where object_id=a.parent_object_id)
  
  
        --Index
        Insert Into #Columns
        Select    a.object_id ,
                a.index_id As Seq,
                3 As DefinitionType,
                'Create '+Case a.is_unique When 1 Then 'Unique ' Else '' End+
                Case a.type When 1 Then 'Clustered ' Else 'Nonclustered ' End+
                'Index '+Quotename(a.name)+' On '+Quotename(b.name)+
                                        ' ('+Stuff((Select ','+Quotename(b1.Name)+Case a1.is_descending_key When 1 Then ' Desc' Else ' Asc' End
                                                        From sys.index_columns As a1
                                                            Inner Join sys.columns As b1 On b1.object_id=a1.object_id And b1.column_id=a1.column_id
                                                        Where a1.object_id=a.object_id And a.index_id=a1.index_id And a1.is_included_column=0
                                                        For Xml Path('')
                                                    ),1,1,'')+
                                        ')'+
                                        Isnull(' Include('+Stuff((Select ','+Quotename(b1.Name)
                                                        From sys.index_columns As a1
                                                            Inner Join sys.columns As b1 On b1.object_id=a1.object_id And b1.column_id=a1.column_id
                                                        Where a1.object_id=a.object_id And a.index_id=a1.index_id And a1.is_included_column=1
                                                        For Xml Path('')
                                                    ),1,1,'')+
                                        ')','')
                    As definition
            From sys.indexes As a
                Inner Join #Objects As b On b.object_id=a.object_id
            Where a.type>0
                    And Not Exists(Select 1 From sys.key_constraints Where parent_object_id=a.object_id And unique_index_id=a.index_id)
  
         
        --extended_properties Andy 2017-3-28 添加扩展属性
        insert into #Columns
            select b.object_id,
                    a.major_id as Seq,
                    4 as DefinitionType,
                    case a.minor_id
                        when 0 then 'execute sp_addextendedproperty ''MS_Description'','''+convert(nvarchar(max),a.value)+''', ''user'', ''dbo'', ''table'', '+quotename(b.name,'''')
                        else 'execute sp_addextendedproperty ''MS_Description'','''+convert(nvarchar(max),a.value)+''', ''user'', ''dbo'', ''table'', '+quotename(b.name,'''')+',''column'','+quotename(c.name,'''')
                        end
                from  sys.extended_properties a
                    inner join #Objects b on b.object_id=a.major_id
                    inner join sys.columns c on c.object_id=b.object_id
                        and c.column_id=a.minor_id
                where a.class=1
         
  
     
  
        --Print
         
/*
        Print 'Use '+Quotename(db_name())+@Enter+'Go'+@Enter+'/* 创建表结构 Andy '+Convert(nvarchar(10),Getdate(),120)+'*/'+@Enter
  
        Set @id=1
        While @id<=@Rowcount
        Begin
            Select @objectid=object_id,@ObjectName=name From #Objects Where id=@id
  
            Set @Sql=@Enter+'--('+Rtrim(@id)+'/'+Rtrim(@Rowcount)+') '+@ObjectName+@Enter+'If object_id('''+Quotename(@ObjectName)+''') Is Null'+@Enter+'Begin'+@Enter+@Tab+
                    'Create Table '+Quotename(@ObjectName)+@Enter+@Tab+'('+@Enter
            Select @Sql=@Sql+@Tab+@Tab+definition+','+@Enter
                From #Columns
                Where object_id=@objectid
                        And DefinitionType=1
                Group By Seq,definition
                Order By Seq
            Set @sql=Substring(@sql,1,Len(@sql)-3)+@Enter+@Tab+')'+@Enter
            Select @Sql=@Sql+@Tab+definition+@Enter
                From #Columns
                Where object_id=@objectid
                        And DefinitionType>1
                Group By DefinitionType,Seq,definition
                Order By Seq
  
            Print Substring(@sql,1,Len(@sql)-2)+@Enter+'End'
            Set @id=@id+1
        End
  
*/
        --Modify Nr:20100510 Start
        Declare @MaxRow int
         
        if object_id('tempdb..#Print') Is Not Null
            Drop Table #Print
  
        Create Table #Print(Row int Identity(1,1) Primary Key,Sql nvarchar(4000))
         
        Print 'Use '+Quotename(db_name())+@Enter+'Go'+@Enter+'/* 创建表结构 Andy '+Convert(nvarchar(10),Getdate(),120)+'*/'+@Enter
  
        Set @id=1
        While @id<=@Rowcount
        Begin
            Select @objectid=object_id,@ObjectName=name From #Objects Where id=@id
                     
            Insert Into #Print(Sql)
                Select @Enter+'--('+Rtrim(@id)+'/'+Rtrim(@Rowcount)+') '+@ObjectName+@Enter+'If object_id('''+Quotename(@ObjectName)+''') Is Null'+@Enter+'Begin'+@Enter+@Tab+
                    'Create Table '+Quotename(@ObjectName)+@Enter+@Tab+'('+@Enter
            
           Insert Into #Print(Sql)
            Select @Tab+@Tab+definition+','+@Enter
                From #Columns
                Where object_id=@objectid
                        And DefinitionType=1
                Group By Seq,definition
                Order By Seq          
             
            Set @MaxRow=Scope_identity()
            Update #Print
                Set Sql=Substring(sql,1,Len(sql)-3)+@Enter+@Tab+')'+@Enter
                Where Row=@MaxRow
        
            Insert Into #Print(Sql)
            Select @Tab+definition+@Enter
                From #Columns
                Where object_id=@objectid
                        And DefinitionType>1
                Group By DefinitionType,Seq,definition
                Order By Seq
             
            if @@ROWCOUNT >0
                Set @MaxRow=Scope_identity()
  
            Update #Print
                Set Sql= Substring(Sql,1,Len(Sql)-2)+@Enter+'End'
                Where Row=@MaxRow
             
            Set @id=@id+1
        End
         
        Set @id=1
        While @id>0
        Begin
            Set @sql=''
            Select @sql=sql From #Print Where row=@id
             
            If @sql>''
            Begin
                Print @sql
                Set @id=@id+1
            end
            Else
                Set @id=0
        End
         
        --Modify Nr:20100510 End
         
         
        Print 'Go'
  
    Drop Table #Columns
    Drop Table #Objects
  
  
Go

 

posted @   ok_008  阅读(538)  评论(1编辑  收藏  举报
编辑推荐:
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
阅读排行:
· 如何给本地部署的DeepSeek投喂数据,让他更懂你
· 超详细,DeepSeek 接入PyCharm实现AI编程!(支持本地部署DeepSeek及官方Dee
· 用 DeepSeek 给对象做个网站,她一定感动坏了
· .NET 8.0 + Linux 香橙派,实现高效的 IoT 数据采集与控制解决方案
· DeepSeek处理自有业务的案例:让AI给你写一份小众编辑器(EverEdit)的语法着色文件
给我写信
点击右上角即可分享
微信分享提示