先创建一个分割表名的分割函数
--表值函数用以截取字符串--如果为其添加一列主键ID,则其顺序就会固定了create FUNCTION [Split](@text NVARCHAR(max@H_301_34@))RETURNS @temptable table(value NVARCHAR(1000@H_301_34@))ASBEGIN DECLARE @StartIndex INT --开始查找的位置 DECLARE @Findindex INT --找到的位置 DECLARE @Content VARCHAR(4000) --找到的值 --初始化一些变量 SET @StartIndex = 1 --T-sql中字符串的查找位置是从1开始的 SET @Findindex=0 --开始循环查找字符串逗号 WHILE(@StartIndex <= LEN(@Text@H_301_34@)) BEGIN --查找字符串函数 CHARINDEX 第一个参数是要找的字符串 -- 第二个参数是在哪里查找这个字符串 -- 第三个参数是开始查找的位置 --返回值是找到字符串的位置 SELECT @Findindex = CHARINDEX(‘,‘,@Text,@StartIndex@H_301_34@) --判断有没找到 没找到返回0 IF(@Findindex =0 OR @Findindex IS NulL@H_301_34@) BEGIN --如果没有找到就表示找完了 SET @Findindex = LEN(@Text)+1 END --截取字符串函数 SUBSTRING 第一个参数是要截取的字符串 -- 第二个参数是开始的位置 -- 第三个参数是截取的长度 SET @Content =SUBSTRING(@Text,@StartIndex,@Findindex-@StartIndex@H_301_34@) --初始化下次查找的位置 SET @StartIndex = @Findindex+1 --把找的的值插入到要返回的table类型中 INSERT INTO @temptable (Value) VALUES (@Content@H_301_34@) END RETURNEND
创建获得建表语句的存储过程
create PROCEDURE [sp_gettext] @name VARCHAR(max) = NulL@H_301_34@,@IDentity BIT = 1@H_301_34@,@index tinyint = 2 -- 0不创建索引 1不创建表 2创建索引 ,@new BIT =0 ASSET ARITHABORT ON@H_301_34@;SET CONCAT_NulL_YIELDS_NulL ON@H_301_34@;SET QUOTED_IDENTIFIER ON@H_301_34@;SET ANSI_NulLS ON@H_301_34@;SET ANSI_padding ON@H_301_34@;SET ANSI_WARNINGS ON@H_301_34@;SET NUMERIC_ROUNDABORT OFF@H_301_34@;DECLARE @crlf CHAR(2@H_301_34@);SET @crlf = CHAR(13) + CHAR(10@H_301_34@);DECLARE @objID INT@H_301_34@;DECLARE @results table (deFinition NVARCHAR(max@H_301_34@))DECLARE @objects table (ID VARCHAR(100),type CHAR(2@H_301_34@));WITH@H_301_34@ db1(dbname) AS (SELECT [value] AS@H_301_34@ dbname FROM Split(@name@H_301_34@)),db2 AS (SELECT --CASE WHEN CHARINDEX(‘.‘,dbname) = 0 AND CHARINDEX(‘[‘,dbname) = 0 THEN ‘[cn9c080].‘ + QUOTEname(dbname) -- ELSE dbname -- END AS dbname CASE WHEN o.[object_ID] IS NulL AND tt.[name] IS NulL THEN ‘%‘ ELSE LTRIM(ISNulL(o.[object_ID],tt.[type_table_object_ID])) END AS object_ID@H_301_34@,CASE [db1].[dbname] WHEN ‘tables‘ THEN ‘U‘ WHEN ‘procs‘ THEN ‘P‘ else@H_301_34@ o.[type] END@H_301_34@ type FROM@H_301_34@ db1 left JOIN sys.[objects] o ON (PARSEname(db1.[dbname],1) = o.[name] OR OBJECT_ID(‘[cn9c080].‘ + QUOTEname(dbname)) = o.[object_ID]@H_301_34@) left JOIN sys.table_types tt ON db1.dbname=@H_301_34@tt.name ) INSERT INTO @objects SELECT * FROM@H_301_34@ db2 ; --OPTION (MAXRECURSION 0); --SELECT top 100 * FROM @objects; WITH@H_301_34@ ColumnDefs AS (SELECT tableObj = c.[object_ID],ColSeq =@H_301_34@ c.column_ID,ColumnDef = QUOTEname(c.name) + ‘ ‘ + CASE WHEN c.is_computed = 1 THEN ‘as ‘ + COALESCE(k.[deFinition],‘‘) + CASE WHEN k.is_persisted = 1 THEN ‘ PERSISTED‘ + CASE WHEN k.is_nullable = 0 THEN ‘ NOT NulL‘ ELSE ‘‘ END ELSE ‘‘ END ELSE DataType + CASE WHEN DataType IN@H_301_34@ ( ‘decimal‘@H_301_34@,‘numeric‘@H_301_34@) THEN ‘(‘ + CAST(c.precision AS VARCHAR(10)) + CASE WHEN c.scale <> 0 THEN ‘,‘ + CAST(c.scale AS VARCHAR(10@H_301_34@)) ELSE ‘‘ END + ‘)‘ WHEN DataType IN (‘char‘@H_301_34@,‘varchar‘@H_301_34@,‘nchar‘@H_301_34@,‘nvarchar‘@H_301_34@,‘binary‘@H_301_34@,‘varbinary‘@H_301_34@) THEN ‘(‘ + CASE WHEN c.max_length = -1 THEN ‘max‘ ELSE CASE WHEN DataType IN@H_301_34@ ( ‘nchar‘@H_301_34@,‘nvarchar‘@H_301_34@) THEN CAST(c.max_length / 2 AS VARCHAR(10@H_301_34@)) ELSE CAST(c.max_length AS VARCHAR(10@H_301_34@)) END END + ‘)‘ WHEN DataType = ‘float‘ AND c.precision <> 53 THEN ‘(‘ + CAST(c.precision AS VARCHAR(10)) + ‘)‘ WHEN DataType IN (‘time‘@H_301_34@,‘datetime2‘@H_301_34@,‘datetimeoffset‘) AND c.scale <> 7 THEN ‘(‘ + CAST(c.scale AS VARCHAR(10)) + ‘)‘ ELSE ‘‘ END END + CASE WHEN c.is_IDentity = 1 AND @IDentity = 1 THEN ‘ IDENTITY(‘ + CAST(IDENT_SEED(QUOTEname(OBJECT_SCHEMA_name(c.[object_ID])) + ‘.‘ + QUOTEname(OBJECT_name(c.[object_ID]))) AS VARCHAR(30)) + ‘,‘ + CAST(IDENT_INCR(QUOTEname(OBJECT_SCHEMA_name(c.[object_ID])) + ‘.‘ + QUOTEname(OBJECT_name(c.[object_ID]))) AS VARCHAR(30)) + ‘)‘ ELSE ‘‘ END + CASE WHEN c.is_rowguIDcol = 1 THEN ‘ ROWGUIDCol‘ ELSE ‘‘ END + CASE WHEN c.xml_collection_ID > 0 THEN ‘ (CONTENT ‘ + QUOTEname(SCHEMA_name(x.schema_ID)) + ‘.‘ + QUOTEname(x.name) + ‘)‘ ELSE ‘‘ END + CASE WHEN c.is_computed = 0 AND UserdefinedFlag = 0 THEN CASE WHEN c.collation_name <> CAST(DATABASEPROPERTYEX(DB_name@H_301_34@(),‘collation‘) AS NVARCHAR(128@H_301_34@)) THEN ‘ ColLATE ‘ +@H_301_34@ c.collation_name ELSE ‘‘ END ELSE ‘‘ END + CASE WHEN c.is_computed = 0 THEN CASE WHEN c.is_nullable = 0 THEN ‘ NOT‘ ELSE ‘‘ END + ‘ NulL‘ ELSE ‘‘ END + CASE WHEN c.default_object_ID > 0 AND ISNulL(@new,0) = 0 THEN ‘ CONSTRAINT ‘ + QUOTEname(d.name) + ‘ DEFAulT ‘ + COALESCE(d.[deFinition]@H_301_34@,‘‘@H_301_34@) WHEN c.default_object_ID > 0 AND ISNulL(@new,0) = 1 THEN ‘ DEFAulT ‘ + COALESCE(d.[deFinition]@H_301_34@,‘‘@H_301_34@) ELSE ‘‘ END FROM@H_301_34@ sys.columns c CROSS APPLY (SELECT DataType =@H_301_34@ TYPE_name(c.user_type_ID),UserdefinedFlag = CASE WHEN c.system_type_ID = c.user_type_ID THEN 0 ELSE 1 END@H_301_34@) F1 left JOIN sys.default_constraints d ON c.default_object_ID = d.[object_ID] left JOIN sys.computed_columns k ON c.[object_ID] = k.[object_ID] AND c.column_ID =@H_301_34@ k.column_ID left JOIN sys.xml_schema_collections x ON c.xml_collection_ID =@H_301_34@ x.xml_collection_ID),IndexDefs AS (SELECT tableObj = i.[object_ID],Ixname = QUOTEname(i.name+CASE WHEN @new=1 THEN ‘_‘+left(NEWID(),4) ELSE ‘‘ end),IxPKFlag =@H_301_34@ i.is_primary_key,IxType = CASE WHEN i.is_primary_key = 1 THEN ‘PRIMARY KEY ‘ WHEN i.is_unique = 1 THEN ‘UNIQUE ‘ ELSE ‘‘ END + LOWER@H_301_34@(type_desc),IxDef = ‘(‘ + IxColList + ‘)‘ + COALESCE(‘ INCLUDE (‘ + IxInclList + ‘)‘,‘‘@H_301_34@),IxOpts =@H_301_34@ IxOptList FROM@H_301_34@ sys.indexes i left JOIN sys.stats s ON i.index_ID = s.stats_ID AND i.[object_ID] = s.[object_ID] CROSS APPLY (SELECT STUFF((SELECT CASE WHEN i.is_padded = 1 THEN ‘,PAD_INDEX=ON‘ ELSE ‘‘ END + CASE WHEN i.fill_factor <> 0 THEN ‘,FILLFACTOR=‘ + CAST(i.fill_factor AS VARCHAR(10@H_301_34@)) ELSE ‘‘ END + CASE WHEN i.ignore_dup_key = 1 THEN ‘,IGnorE_DUP_KEY=ON‘ ELSE ‘‘ END + CASE WHEN s.no_recompute = 1 THEN ‘,STATISTICS_RECOmpuTE=ON‘ ELSE ‘‘ END + CASE WHEN i.allow_row_locks = 0 THEN ‘,ALLOW_ROW_LOCKS=OFF‘ ELSE ‘‘ END + CASE WHEN i.allow_page_locks = 0 THEN ‘,ALLOW_PAGE_LOCKS=OFF‘ ELSE ‘‘ END),1,2,‘‘@H_301_34@)) F_IxOpts (IxOptList) CROSS APPLY (SELECT STUFF((SELECT ‘,‘ + QUOTEname(c.name) + CASE WHEN ic.is_descending_key = 1 AND i.type <> 3 THEN ‘ DESC‘ WHEN ic.is_descending_key = 0 AND i.type <> 3 THEN ‘ ASC‘ ELSE ‘‘ END FROM@H_301_34@ sys.index_columns ic JOIN sys.columns c ON ic.[object_ID] = c.[object_ID] AND ic.column_ID =@H_301_34@ c.column_ID WHERE ic.[object_ID] = i.[object_ID] AND ic.index_ID = i.index_ID AND ic.is_included_column = 0 ORDER BY@H_301_34@ ic.key_ordinal FOR XML PATH(‘‘@H_301_34@),TYPE).value(‘.‘,‘nvarchar(max)‘),‘‘@H_301_34@)) F_IxCols (IxColList) CROSS APPLY (SELECT STUFF((SELECT ‘,‘ + QUOTEname@H_301_34@(c.name) FROM@H_301_34@ sys.index_columns ic JOIN sys.columns c ON ic.[object_ID] = c.[object_ID] AND ic.column_ID =@H_301_34@ c.column_ID WHERE ic.[object_ID] = i.[object_ID] AND ic.index_ID = i.index_ID AND ic.is_included_column = 1 ORDER BY@H_301_34@ ic.key_ordinal FOR XML PATH(‘‘@H_301_34@),‘‘@H_301_34@)) F_IxIncl (IxInclList) WHERE i.type_desc <> ‘HEAP‘@H_301_34@),FKDefs AS (SELECT tableObj = f.parent_object_ID,FKname = QUOTEname@H_301_34@(f.name),FKRef = QUOTEname(OBJECT_SCHEMA_name(f.referenced_object_ID)) + ‘.‘ + QUOTEname(OBJECT_name@H_301_34@(f.referenced_object_ID)),FKColList = ParentColList,FKRefList =@H_301_34@ RefColList,FKDelOpt = CASE@H_301_34@ f.delete_referential_action WHEN 1 THEN ‘CASCADE‘ WHEN 2 THEN ‘SET NulL‘ WHEN 3 THEN ‘SET DEFAulT‘ END,FKUpdOpt = CASE@H_301_34@ f.update_referential_action WHEN 1 THEN ‘CASCADE‘ WHEN 2 THEN ‘SET NulL‘ WHEN 3 THEN ‘SET DEFAulT‘ END,FKnorepl =@H_301_34@ f.is_not_for_replication FROM@H_301_34@ sys.foreign_keys f CROSS APPLY (SELECT STUFF((SELECT ‘,‘ + QUOTEname@H_301_34@(c.name) FROM@H_301_34@ sys.foreign_key_columns k JOIN sys.columns c ON k.parent_object_ID = c.[object_ID] AND k.parent_column_ID =@H_301_34@ c.column_ID WHERE k.constraint_object_ID = f.[object_ID] ORDER BY@H_301_34@ constraint_column_ID FOR XML PATH(‘‘@H_301_34@),‘‘@H_301_34@)) F_Parent (ParentColList) CROSS APPLY (SELECT STUFF((SELECT ‘,‘ + QUOTEname@H_301_34@(c.name) FROM@H_301_34@ sys.foreign_key_columns k JOIN sys.columns c ON k.referenced_object_ID = c.[object_ID] AND k.referenced_column_ID =@H_301_34@ c.column_ID WHERE k.constraint_object_ID = f.[object_ID] ORDER BY@H_301_34@ constraint_column_ID FOR XML PATH(‘‘@H_301_34@),‘‘@H_301_34@)) F_Ref (RefColList)) INSERT INTO @results ([deFinition]@H_301_34@) SELECT --tablename, [deFinition]+CHAR(10)+‘GO‘+CHAR(10) AS@H_301_34@ deFinition FROM@H_301_34@ sys.tables t INNER JOIN @objects o ON(t.[object_ID] liKE o.[ID] AND o.[type]=‘U‘@H_301_34@) CROSS APPLY (SELECT tablename = QUOTEname(OBJECT_SCHEMA_name(t.[object_ID])) + ‘.‘ + QUOTEname(OBJECT_name(t.[object_ID]@H_301_34@))) F_name CROSS APPLY (SELECT STUFF((SELECT @crlf + ‘,‘ +@H_301_34@ ColumnDef FROM@H_301_34@ ColumnDefs WHERE tableObj = t.[object_ID] ORDER BY@H_301_34@ ColSeq FOR XML PATH(‘‘@H_301_34@),5,‘‘@H_301_34@)) F_Cols (ColumnList) CROSS APPLY (SELECT STUFF((SELECT @crlf + ‘,CONSTRAINT ‘ + QUOTEname(name) + ‘ CHECK ‘ + CASE WHEN is_not_for_replication = 1 THEN ‘NOT FOR REPliCATION ‘ ELSE ‘‘ END + COALESCE([deFinition]@H_301_34@,‘‘@H_301_34@) FROM@H_301_34@ sys.check_constraints WHERE parent_object_ID = t.[object_ID] FOR XML PATH(‘‘@H_301_34@),‘‘@H_301_34@)) F_Const (ChkConstList) CROSS APPLY (SELECT STUFF((SELECT @crlf + ‘,CONSTRAINT ‘ + Ixname + ‘ ‘ + IxType + ‘ ‘ + IxDef + COALESCE(‘ WITH (‘ + IxOpts + ‘)‘@H_301_34@,‘‘@H_301_34@) FROM@H_301_34@ IndexDefs WHERE tableObj = t.[object_ID] AND IxPKFlag = 1 FOR XML PATH(‘‘@H_301_34@),‘‘@H_301_34@)) F_IxConst (IxConstList) CROSS APPLY (SELECT STUFF((SELECT @crlf + ‘,CONSTRAINT ‘ + FKname + ‘ FOREIGN KEY ‘ + ‘(‘ + FKColList + ‘)‘ + ‘ REFERENCES ‘ + FKRef + ‘ (‘ + FKRefList + ‘)‘ + CASE WHEN FKDelOpt IS NOT NulL THEN ‘ ON DELETE ‘ +@H_301_34@ FKDelOpt ELSE ‘‘ END + CASE WHEN FKUpdOpt IS NOT NulL THEN ‘ ON UPDATE ‘ +@H_301_34@ FKUpdOpt ELSE ‘‘ END + CASE WHEN FKnorepl = 1 THEN ‘ NOT FOR REPliCATION‘ ELSE ‘‘ END FROM@H_301_34@ FKDefs WHERE tableObj = t.[object_ID] FOR XML PATH(‘‘@H_301_34@),‘‘@H_301_34@)) F_Keys (FKConstList) CROSS APPLY (SELECT STUFF((SELECT @crlf + ‘CREATE ‘ + IxType + ‘ INDEX ‘ + Ixname + ‘ ON ‘ + tablename + ‘ ‘ + IxDef + COALESCE(‘ WITH (‘ + IxOpts + ‘)‘@H_301_34@,‘‘@H_301_34@) FROM@H_301_34@ IndexDefs WHERE tableObj = t.[object_ID] AND IxPKFlag = 0 FOR XML PATH(‘‘@H_301_34@),‘‘@H_301_34@)) F_Indexes (IndexList) CROSS APPLY (SELECT [deFinition] =@H_301_34@ ( SELECT CASE WHEN @index <> 1 THEN ‘CREATE table ‘ + tablename + @crlf + ‘(‘ + @crlf + ‘ ‘ + ColumnList + COALESCE(@crlf +@H_301_34@ ChkConstList,‘‘) + COALESCE(@crlf +@H_301_34@ IxConstList,‘‘) + COALESCE(@crlf +@H_301_34@ FKConstList,‘‘) + @crlf + ‘)‘ + @crlf ELSE ‘‘ END + CASE WHEN @index <> 0 THEN COALESCE(@crlf + IndexList,‘‘@H_301_34@) ELSE ‘‘ END FOR XML PATH(‘‘@H_301_34@),‘nvarchar(max)‘@H_301_34@)) F_link WHERE t.[is_ms_shipped] = 0 AND [deFinition] <> ‘‘@H_301_34@; -- 视图/过程/函数/触发器 INSERT INTO @results ([deFinition]@H_301_34@) SELECT --QUOTEname(object_schema_name(m.object_ID))+‘.‘+ QUOTEname(object_name(m.object_ID)) AS [name],o.type, m.deFinition+CHAR(10)+‘GO‘+CHAR(10) AS@H_301_34@ deFinition FROM@H_301_34@ sys.sql_modules m INNER JOIN sys.objects o ON m.object_ID = o.object_ID INNER JOIN @objects a ON(m.[object_ID] liKE a.[ID] AND a.[type] <>‘U‘@H_301_34@); --houpeIDong 2018-05-30 add 字段说明 insert into @results ([deFinition]@H_301_34@) select ‘EXEC sys.sp_addextendedproperty @name=N‘‘MS_Description‘‘,@value=N‘‘‘ + cast(ep.[value] as varchar(100@H_301_34@)) +‘‘‘,@level0type=N‘‘SCHEMA‘‘,@level0name=N‘‘‘ +@H_301_34@schema_name(schema_ID) +‘‘‘,@level1type=N‘‘table‘‘,@level1name=N‘‘‘ +t.[name] +‘‘‘,@level2type=N‘‘ColUMN‘‘,@level2name=N‘‘‘ +c.[name] +‘‘‘‘ +CHAR(10)+‘GO‘+CHAR(10@H_301_34@) --,t.[name] AS 表名,c.[name] AS 字段名,cast(ep.[value] as varchar(100)) AS [字段说明],c.collation_name,t.create_date,t.modify_date,schema_name(schema_ID),* FROM sys.tables AS@H_301_34@ t INNER JOIN sys.columns AS c ON t.object_ID = c.object_ID left JOIN sys.extended_propertIEs AS ep ON ep.major_ID = c.object_ID AND ep.minor_ID =@H_301_34@ c.column_ID INNER JOIN @objects o ON(t.[object_ID] liKE o.[ID] AND o.[type]=‘U‘@H_301_34@) WHERE ep.class =1 --AND t.name=‘tbCertifyUserApply‘ SELECT * FROM @results@H_301_34@; -- 用户定义类型. 不过对用户定义表类型尚不完善 --;WITH TypeDef AS( -- SELECT Typename=QUOTEname( SCHEMA_name(t.schema_ID))+‘.‘+QUOTEname(t.name) --,Parentname=TYPE_name(t.system_type_ID)+‘‘ -- +case -- when DataType in (‘decimal‘,‘numeric‘) then ‘(‘+cast(t.precision as varchar(10))+case when t.scale<>0 then ‘,‘+cast(t.scale as varchar(10)) else ‘‘ end +‘)‘ -- when DataType in (‘char‘,‘varchar‘,‘nchar‘,‘nvarchar‘,‘binary‘,‘varbinary‘) then ‘(‘+case when t.max_length=-1 then ‘max‘ else case when DataType in (‘nchar‘,‘nvarchar‘) then cast(t.max_length/2 as varchar(10)) else cast(t.max_length as varchar(10)) end end +‘)‘ -- when DataType=‘float‘ and t.precision<>53 then ‘(‘+cast(t.precision as varchar(10))+‘)‘ -- when DataType in (‘time‘,‘datetime2‘,‘datetimeoffset‘) and t.scale<>7 then ‘(‘+cast(t.scale as varchar(10))+‘)‘ -- else ‘‘ -- end -- +case when t.is_nullable=0 then ‘ NOT‘ else ‘‘ end+‘ NulL‘ -- from sys.types t -- cross apply ( -- select DataType=type_name(t.system_type_ID) -- ) F1 -- WHERE t.is_user_defined=1 -- ) -- SELECT Typename,‘CREATE TYPE ‘+Typename+‘ FROM ‘ +Parentname AS deFinition FROM TypeDef
执行示例：
--单个表exec [sp_gettext] ‘tablename‘--多个表exec [sp_gettext] ‘tablename1,tablename2,tablename3‘总结
以上是内存溢出为你收集整理的sqlserver 建表语句，获取建表语句的存储过程，包括排序规则，索引，字段说明，支持同时生成多个表全部内容，希望文章能够帮你解决sqlserver 建表语句，获取建表语句的存储过程，包括排序规则，索引，字段说明，支持同时生成多个表所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）