织梦网站上传及安装,建设人员变更是哪个网站,局域网里做网站,点拓网站建设已经很久没用使用这个脚本了#xff0c;今天用到#xff0c;并做修改#xff0c;增加了生成扩展属性功能。 Go
if object_ID([up_CreateTable]) is not nullDrop Procedure [up_CreateTable]
Go
/* 生成建表脚本(V4.0) Andy 2017-3-28 */
Create Proc up_CreateTable
(obje… 已经很久没用使用这个脚本了今天用到并做修改增加了生成扩展属性功能。 Go
if object_ID([up_CreateTable]) is not nullDrop 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_propertiesModify: andy 2017-3-28 增加了扩展属性
*/
Set Nocount OnDeclare sql nvarchar(max),objectid int,id int,Rowcount int,ObjectName sysname,Enter nvarchar(2),Tab nvarchar(2)Select EnterChar(13)Char(10),TabChar(9) Declare Tmp Table(name sysname)If objectListBeginSet sqlSelect NReplace(objectList,,, Union All Select N)Insert Into Tmp (name) Exec(sql)Set sqlnullSelect sqlIsnull(sql,,)name From Tmp As aWhere Not Exists(Select 1 From sys.objects Where typeU And namea.name)If sqlBeginSet sql发现无效的表名: sqlRaiserror (50001,-1,-1, sql)Return(1)EndEndIf object_id(tempdb..#Objects) Is Not NullDrop Table #ObjectsIf object_id(tempdb..#Columns) Is Not NullDrop 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_nameFrom sys.objects aWhere TypeU And is_ms_shipped0 And Not Exists(Select 1 From sys.foreign_keys Where referenced_object_ida.object_id)Union AllSelect a.referenced_object_id As Object_id,b.LevelNo1 As LevelNo,c.name As object_nameFrom sys.foreign_keys aInner Join t b On b.object_ida.parent_object_idInner Join sys.objects c On c.object_ida.referenced_object_id And c.is_ms_shipped0where a.referenced_object_ida.parent_object_id)Insert Into #Objects(object_id,name)Select a.object_id,object_nameFrom t aWhere Not Exists(Select 1 From t Where object_ida.object_id And LevelNoa.LevelNo) AndNot Exists(Select 1 From sys.extended_properties Where major_ida.object_id And minor_id0 And class1 And NameNmicrosoft_database_tools_support)And (Exists(Select 1 From Tmp Where namea.object_name) Or Not Exists(Select 1 From Tmp))Group By object_id,object_name,LevelNoOrder By LevelNo DescSet RowcountRowcountIf Rowcount0Begin-- Raiserror 50001 N没有可以生产脚本的表!Raiserror (50001,-1,-1, N没有可以生产脚本的表!)Return(1)End--ColumnSelect 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 EndIsnull(Constraint Quotename(e.name) Default(e.definition),) As definitionInto #ColumnsFrom sys.columns As aInner Join #Objects As b On b.object_ida.object_idInner Join sys.types As c On c.user_type_ida.user_type_idLeft Outer Join sys.identity_columns As d On d.object_ida.object_id And d.column_ida.column_id And a.is_identity1Left Outer Join sys.Default_constraints As e On e.object_ida.default_object_id And e.parent_column_ida.column_idCreate Nonclustered Index IX_#Columns_object_id On #Columns(object_id Asc)--ConstraintInsert Into #ColumnsSelect 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_ida.parent_object_id And is_primary_key1 And type1) Then NClustered Else NNonclustered End(Stuff((Select ,Quotename(c1.Name)Case a1.is_descending_key When 1 Then Desc Else Asc EndFrom sys.index_columns As a1Inner Join sys.indexes As b1 On b1.object_ida1.object_id And b1.index_ida1.index_id And b1.is_primary_key1Inner Join sys.columns As c1 On c1.object_ida1.object_id And c1.column_ida1.column_idWhere a1.object_ida.parent_object_id For Xml Path()),1,1,))When F Then Foreign Key (Stuff((Select ,Quotename(b1.Name)From sys.foreign_key_columns As a1Inner Join sys.columns As b1 On b1.object_ida1.parent_object_id And b1.column_ida1.parent_column_idWhere a1.constraint_object_ida.object_id Order By a1.constraint_column_idFor Xml Path()),1,1,)) References (Select Quotename(object_name(referenced_object_id)) From sys.foreign_keys Where object_ida.object_id) (Stuff((Select ,Quotename(b1.Name)From sys.foreign_key_columns As a1Inner Join sys.columns As b1 On b1.object_ida1.referenced_object_id And b1.column_ida1.referenced_column_idWhere a1.constraint_object_ida.object_id Order By a1.constraint_column_idFor Xml Path()),1,1,))When UQ Then Unique(Select Case a1.type When 1 Then Clustered Else Nonclustered EndFrom sys.indexes As a1Where a1.object_ida.parent_object_id And Exists(Select 1 From sys.key_constraints Where object_ida.object_id And parent_object_ida1.object_id And unique_index_ida1.index_id)) (Stuff((Select ,Quotename(c1.Name)Case a1.is_descending_key When 1 Then Desc Else Asc EndFrom sys.index_columns As a1Inner Join sys.indexes As b1 On b1.object_ida1.object_id And b1.index_ida1.index_id And b1.is_unique_constraint1Inner Join sys.columns As c1 On c1.object_ida1.object_id And c1.column_ida1.column_idWhere a1.object_ida.parent_object_id And Exists(Select 1 From sys.key_constraints Where object_ida.object_id And parent_object_ida1.object_id And unique_index_ida1.index_id)For Xml Path()),1,1,))When C Then Check (Select definition From sys.check_constraints Where object_ida.object_id)Else End As definitionFrom sys.objects As aWhere a.type In(PK,F,C,UQ)And Exists(Select 1 From #Objects Where object_ida.parent_object_id)--IndexInsert Into #ColumnsSelect a.object_id ,a.index_id As Seq,3 As DefinitionType,Create Case a.is_unique When 1 Then Unique Else EndCase a.type When 1 Then Clustered Else Nonclustered EndIndex Quotename(a.name) On Quotename(b.name) (Stuff((Select ,Quotename(b1.Name)Case a1.is_descending_key When 1 Then Desc Else Asc EndFrom sys.index_columns As a1Inner Join sys.columns As b1 On b1.object_ida1.object_id And b1.column_ida1.column_idWhere a1.object_ida.object_id And a.index_ida1.index_id And a1.is_included_column0For Xml Path()),1,1,))Isnull( Include(Stuff((Select ,Quotename(b1.Name)From sys.index_columns As a1Inner Join sys.columns As b1 On b1.object_ida1.object_id And b1.column_ida1.column_idWhere a1.object_ida.object_id And a.index_ida1.index_id And a1.is_included_column1For Xml Path()),1,1,)),)As definitionFrom sys.indexes As aInner Join #Objects As b On b.object_ida.object_idWhere a.type0And Not Exists(Select 1 From sys.key_constraints Where parent_object_ida.object_id And unique_index_ida.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_ida.major_idinner join sys.columns c on c.object_idb.object_idand c.column_ida.minor_idwhere a.class1--Print/*Print Use Quotename(db_name())EnterGoEnter/* 创建表结构 Andy Convert(nvarchar(10),Getdate(),120)*/EnterSet id1While idRowcountBeginSelect objectidobject_id,ObjectNamename From #Objects Where ididSet SqlEnter--(Rtrim(id)/Rtrim(Rowcount)) ObjectNameEnterIf object_id(Quotename(ObjectName)) Is NullEnterBeginEnterTabCreate Table Quotename(ObjectName)EnterTab(EnterSelect SqlSqlTabTabdefinition,EnterFrom #Columns Where object_idobjectid And DefinitionType1Group By Seq,definitionOrder By SeqSet sqlSubstring(sql,1,Len(sql)-3)EnterTab)EnterSelect SqlSqlTabdefinitionEnterFrom #Columns Where object_idobjectid And DefinitionType1Group By DefinitionType,Seq,definitionOrder By SeqPrint Substring(sql,1,Len(sql)-2)EnterEndSet idid1End*/--Modify Nr:20100510 StartDeclare MaxRow intif object_id(tempdb..#Print) Is Not NullDrop Table #PrintCreate Table #Print(Row int Identity(1,1) Primary Key,Sql nvarchar(4000))Print Use Quotename(db_name())EnterGoEnter/* 创建表结构 Andy Convert(nvarchar(10),Getdate(),120)*/EnterSet id1While idRowcountBeginSelect objectidobject_id,ObjectNamename From #Objects Where ididInsert Into #Print(Sql)Select Enter--(Rtrim(id)/Rtrim(Rowcount)) ObjectNameEnterIf object_id(Quotename(ObjectName)) Is NullEnterBeginEnterTabCreate Table Quotename(ObjectName)EnterTab(Enter Insert Into #Print(Sql)Select TabTabdefinition,EnterFrom #Columns Where object_idobjectid And DefinitionType1Group By Seq,definitionOrder By Seq Set MaxRowScope_identity()Update #PrintSet SqlSubstring(sql,1,Len(sql)-3)EnterTab)EnterWhere RowMaxRowInsert Into #Print(Sql)Select TabdefinitionEnterFrom #Columns Where object_idobjectid And DefinitionType1Group By DefinitionType,Seq,definitionOrder By Seqif ROWCOUNT 0 Set MaxRowScope_identity()Update #PrintSet Sql Substring(Sql,1,Len(Sql)-2)EnterEndWhere RowMaxRow Set idid1EndSet id1While id0BeginSet sqlSelect sqlsql From #Print Where rowidIf sqlBeginPrint sqlSet idid1endElseSet id0End--Modify Nr:20100510 EndPrint GoDrop Table #ColumnsDrop Table #ObjectsGo转载于:https://www.cnblogs.com/wghao/p/6636008.html
