MS SQLSERVER 中如何得到表的创建语句
发布时间：2020-12-12 15:44:19 所属栏目：MsSql教程 来源：网络整理
导读：MS SQLSERVER 只能得到存储过程的创建语句,方法如下: sp_helptext procedureName 但是往往我们需要得到表的创建语句,比如说在数据库升级的时候判断某个表是否已经改变,或者已经有一个表存在,但不知道它的创建语句是什么,字段有没有约束,有没有主键,创建了哪
|
MS SQLSERVER 只能得到存储过程的创建语句,方法如下: sp_helptext procedureName 但是往往我们需要得到表的创建语句,比如说在数据库升级的时候判断某个表是否已经改变,或者已经有一个表存在,但不知道它的创建语句是什么,字段有没有约束,有没有主键,创建了哪些索引等等.下面我给出一个存储过程,供读者参考. 该存储过程可以得到你想得到的所有的表的创建语句,包括和表有关的索引的创建语句. SQLSERVER2000 下的代码: create procedure SP_GET_TABLE_INFO @ObjName varchar(128)?????? /* The table to generate sql script */ as declare @Script varchar(255) declare @ColName varchar(30) declare @ColID?? TinyInt declare @UserType smallint declare @TypeName sysname declare @Length?? TinyInt declare @Prec???? TinyInt declare @Scale??? TinyInt declare @Status?? TinyInt declare @cDefault int declare @DefaultID TinyInt declare @Const_Key varchar(255) declare @IndID???? SmallInt ? declare @IndStatus Int declare @Index_Key varchar(255) declare @DBName??? varchar(30) declare @strPri_Key varchar (255) /* **? Check to see the the table exists and initialize @objid. */ if not Exists(Select name from sysobjects where name = @ObjName) begin ? select @DBName = db_name() ??? raiserror(15009,-1,@ObjName,@DBName) ??? return (1) end create table #spscript ( ??? id???? int IDENTITY not null,??? Script Varchar(255) NOT NULL,??? LastLine tinyint ) declare Cursor_Column INSENSITIVE CURSOR ? for Select a.name,a.ColID,a.usertype,b.name,a.length,a.prec,a.scale,a.Status,a.cDefault,??????? case a.cdefault when 0 then ' ' else (select c.Text from syscomments c where a.cdefault = c.id) end const_key ??????? from syscolumns a,systypes b where object_name(a.id) = @ObjName ??????? and a.usertype = b.usertype order by a.ColID set nocount on Select @Script = 'Create table ' + @ObjName + '(' Insert into #spscript values(@Script,0) /* Get column information */ open Cursor_Column fetch next from Cursor_Column into @ColName,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale,????? @Status,@cDefault,@Const_Key Select @Script = '' while (@@FETCH_STATUS <> -1) begin ? if (@@FETCH_STATUS <> -2) ? begin ??? Select @Script = @ColName + ' ' + @TypeName ??? if @UserType in (1,2,3,4) ????? Select @Script = @Script + '(' + Convert(char(3),@Length) + ') ' ??? else if @UserType in (24) ????? Select @Script = @Script + '(' + Convert(char(3),@Prec) + ',' ????????????????????? + Convert(char(3),@Scale) + ') ' ??? else ????? Select @Script = @Script + ' ' ??? if ( @Status & 0x80 ) > 0 ????? Select @Script = @Script + ' IDENTITY(1,1) ' ??? if ( @Status & 0x08 ) > 0 ????? Select @Script = @Script + ' NULL ' ??? else ????? Select @Script = @Script + ' NOT NULL ' ??? if @cDefault > 0 ????? Select @Script = @Script + ' DEFAULT ' + @Const_Key ? end ? fetch next from Cursor_Column into @ColName,@Const_Key ? if @@FETCH_STATUS = 0 ? begin ??? Select @Script = @Script + ',' ??? Insert into #spscript values(@Script,0) ? end ? else ? begin ??? Insert into #spscript values(@Script,1) ??? Insert into #spscript values(')',0) ? end end Close Cursor_Column Deallocate Cursor_Column /* Get index information */ Declare Cursor_Index INSENSITIVE CURSOR ? for Select name,IndID,status from sysindexes where object_name(id)=@ObjName ????????????? and IndID > 0 and IndID<>255? order by IndID?? /*增加了对InDid为255的判断*/ Open Cursor_Index Fetch Next from Cursor_Index into @ColName,@IndID,@IndStatus while (@@FETCH_STATUS <> -1) begin ? if @@FETCH_STATUS <> -2 ? begin ??? declare @i TinyInt ??? declare @thiskey varchar(50) ??? declare @IndDesc varchar(68) /* string to build up index desc in */ ??? Select? @i = 1 ??? while (@i <= 16) ??? begin ????? select @thiskey = index_col(@ObjName,@i) ????? if @thiskey is null ??????? break ????? if @i = 1 ??????? select @Index_Key = index_col(@ObjName,@i) ????? else ??????? select @Index_Key = @Index_Key + ',' + index_col(@ObjName,@i) ????? select @i = @i + 1 ??? end ??? if (@IndStatus & 0x02) > 0 ????? Select @Script = 'Create unique ' ??? else ????? Select @Script = 'Create ' ??? if @IndID = 1 ????? select @Script = @Script + ' clustered ' ??? if (@IndStatus & 0x800) > 0 ???? select @strPri_Key = ' PRIMARY KEY (' + @Index_Key + ')' ??? else ???? select @strPri_Key = '' ??? ? ??? if @IndID > 1 ????? select @Script = @Script + ' nonclustered ' ??? Select @Script = @Script + ' index ' + @ColName + ' ON '+ @ObjName ?????????? + '(' + @Index_Key + ')' ??? Select @IndDesc = '' ??? /* ?**? See if the index is ignore_dupkey (0x01). ??? */ ??? if @IndStatus & 0x01 = 0x01 ????? Select @IndDesc = @IndDesc + ' IGNORE_DUP_KEY' + ',' ??? /* ???? **? See if the index is ignore_dup_row (0x04). ??? */ ?? /* if @IndStatus & 0x04 = 0x04 */ ?? /*?? Select @IndDesc = @IndDesc + ' IGNORE_DUP_ROW' + ',' */ /* 2000 不在支持*/ ??? /* ?**? See if the index is allow_dup_row (0x40). ??? */ ??? if @IndStatus & 0x40 = 0x40 ????? Select @IndDesc = @IndDesc + ' ALLOW_DUP_ROW' + ',' ??? if @IndDesc <> '' ??? begin ????? Select @IndDesc = SubString( @IndDesc,1,DataLength(@IndDesc) - 1 ) ????? Select @Script = @Script + ' WITH ' + @IndDesc ??? end ??? /* ?**? Add the location of the data. ??? */ ? end ? if (@strPri_Key = '') ??? Insert into #spscript values(@Script,0) ? else ??? update #spscript set Script = Script + @strPri_Key where LastLine = 1 ? ? Fetch Next from Cursor_Index into @ColName,@IndStatus end Close Cursor_Index Deallocate Cursor_Index Select Script from #spscript set nocount off return (0)（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |