生成建表脚本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 1 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 |
分类:
SQL Server
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
· 如何给本地部署的DeepSeek投喂数据,让他更懂你
· 超详细,DeepSeek 接入PyCharm实现AI编程!(支持本地部署DeepSeek及官方Dee
· 用 DeepSeek 给对象做个网站,她一定感动坏了
· .NET 8.0 + Linux 香橙派,实现高效的 IoT 数据采集与控制解决方案
· DeepSeek处理自有业务的案例:让AI给你写一份小众编辑器(EverEdit)的语法着色文件