 |
|
|
 首页 |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
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,-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,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale, @Status,@cDefault,@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, @IndID, @i) if @thiskey is null break if @i = 1 select @Index_Key = index_col(@ObjName, @IndID, @i) else select @Index_Key = @Index_Key + \\\', \\\' + index_col(@ObjName, @IndID, @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, @IndID, @IndStatus end Close Cursor_Index Deallocate Cursor_Index Select Script from #spscript set nocount off return (0) SQLSERVER6.5下的代码 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 SmallInt declare @Index_Key varchar(255) declare @Segment SmallInt 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,-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 case c.text when "(\\\' \\\')" then "(\\\'\\\')" else c.text end 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,@ColID,@UserType,@TypeName,@Length,@Prec,@Scale, @Status,@cDefault,@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,Segment from sysindexes where object_name(id)=@ObjName and IndID > 0 and IndID<>255 order by IndID Open Cursor_Index Fetch Next from Cursor_Index into @ColName, @IndID, @IndStatus, @Segment 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, @IndID, @i) if @thiskey is null break if @i = 1 select @Index_Key = index_col(@ObjName, @IndID, @i) else select @Index_Key = @Index_Key + \\\', \\\' + index_col(@ObjName, @IndID, @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\\\' + \\\',\\\' /* ** 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. */ if @Segment <> 1 select @Script = @Script + \\\' ON \\\' + name from syssegments where segment = @Segment 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, @IndID, @IndStatus, @Segment end Close Cursor_Index Deallocate Cursor_Index Select Script from #spscript order by id set nocount off return (0) 返回类别: 教程 上一教程: 常常用到的交叉表问题,一般用动态SQL能生成动态列！ 下一教程: 数据库正规化和设计技巧 您可以阅读与"MS SQL SERVER中如何得到表的创建语句"相关的教程: · MS SQL SERVER中如何迅速获取表的记录总数 · sql server关于函数中如何使用Getdate() · SQL SERVER关于函数中如何使用GETDATE() · 如何获取SQL SERVER数据库里表的占用容量大小 · 如何得到当前所在的SQL的数据库名称 |
  |
| 快精灵印艺坊 版权所有 |
首页 会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
|
||
