sql: sq_helptext

--查看表生成脚本USE  [master]   GO  SET  ANSI_NULLS  ON   GO   SET  QUOTED_IDENTIFIER  ON   GO   /*  ****************************************************************************    功能描述:  获取指定表的创建脚本,包括表和字段的属性、外键(注释掉的)   ----------------------------------------------------------------------------    参数列表:            1:  @TableName  需要创建脚本的表的名称   ****************************************************************************  */  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_HelpTable]') AND type in (N'P', N'PC'))DROP PROCEDURE [dbo].[sp_HelpTable]GO  CREATE  PROCEDURE  [dbo].[sp_HelpTable](@TableName  sysname)   AS     SET  NOCOUNT  ON     DECLARE  @ObjectID  int     DECLARE  @TableScript  table(Iden  int  IDENTITY(1,  1),  ScriptLine  nvarchar(4000))     SET  @ObjectID  =  object_id(@TableName)     IF  @ObjectID  IS  NULL  OR  OBJECTPROPERTY(@ObjectID,  'IsTable')  =  0     BEGIN       RAISERROR('指定的对象不是表对象',  16,  1)       RETURN     END      --获取表的创建脚本     --插入表头     INSERT  INTO  @TableScript(ScriptLine)       SELECT  N'CREATE  TABLE  ['  +  USER_NAME(OBJECTPROPERTY(@ObjectID,  N'OwnerId'))  +  N'].['  +  object_name(@ObjectID)  +  N']('       --插入字段     INSERT  INTO  @TableScript(ScriptLine)       SELECT  N'   ['  +  a.Name  +  N']  ['  +  b.name  +  N']'  +               CASE  WHEN  c.Object_id  IS  NOT  NULL  THEN  N'  IDENTITY('  +  CONVERT(nvarchar,  c.seed_value)  +  N',  '  +  CONVERT(nvarchar,  c.increment_value)  +  N')'                    ELSE  ''  END  +              CASE  WHEN  b.xusertype  IN  (167,  175,  231,  239)  THEN  N'('+CONVERT(nvarchar,  a.prec)   +  N')'                   WHEN  b.xusertype  in  (106,  108)            THEN  N'('+CONVERT(nvarchar,  a.xprec)  +  N',  '  +  CONVERT(nvarchar,  a.xscale)  +  N')'                   ELSE  ''  END  +              CASE  a.isnullable  WHEN  1  THEN  N''  ELSE  N'  NOT'  END  +  N'  NULL'  +              CASE  WHEN  d.Name  IS  NOT  NULL  THEN  N'  DEFAULT  '  +  d.Definition  ELSE  N''  END  +              N','          FROM  sys.syscolumns  a           LEFT  JOIN  sys.systypes  b  ON  a.xusertype  =  b.xusertype           LEFT  JOIN  sys.identity_columns  c  ON  c.Object_id  =  a.ID  AND  c.Column_ID  =  a.ColID           LEFT  JOIN  sys.default_constraints  d  ON  d.Parent_Object_ID  =  a.ID  AND  d.Parent_column_ID  =  a.ColID       WHERE  a.[ID]  =  @ObjectID       ORDER  BY  a.ColOrder       --插入主键和索引     DECLARE  @IndexID  int,  @IndexScript  nvarchar(4000)     DECLARE  IndexCursor  CURSOR  FOR        SELECT  b.Index_ID,  N'   CONSTRAINT  ['  +  a.Name  +  N']  '  +               CASE  a.Type  WHEN  'PK'  THEN  N'PRIMARY  KEY  '  WHEN  'UQ'  THEN  N'UNIQUE  '  END  +               CASE  b.Type  WHEN  1     THEN  N'CLUSTERED'    WHEN  2      THEN  N'NONCLUSTERED  '  END  +  N'('         FROM  sys.key_constraints  a           LEFT  JOIN  sys.indexes  b  ON  b.Object_ID  =  a.Parent_Object_ID  AND  b.index_id  =  a.unique_index_id       WHERE  a.Parent_Object_ID  =  @ObjectID     OPEN  IndexCursor     FETCH  NEXT  FROM  IndexCursor  INTO  @IndexID,  @IndexScript     WHILE  @@FETCH_STATUS  =  0     BEGIN       SELECT  @IndexScript  =  @IndexScript  +  N'['  +  INDEX_COL(object_name(@ObjectID),  2  ,  1)  +  N'],'         FROM  sys.index_columns        WHERE  Object_ID  =  @ObjectID         AND  Index_ID  =  2             SET  @IndexScript  =  LEFT(@IndexScript,  LEN(@IndexScript)  -1)  +  N'),'       INSERT  INTO  @TableScript(ScriptLine)  VALUES(@IndexScript)         FETCH  NEXT  FROM  IndexCursor  INTO  @IndexID,  @IndexScript     END     CLOSE  IndexCursor     DEALLOCATE  IndexCursor       --除去最后一个,号     UPDATE  @TableScript        SET  ScriptLine  =  LEFT(ScriptLine,  LEN(ScriptLine)  -  1)     WHERE  Iden  =  (SELECT  MAX(Iden)  FROM  @TableScript)     INSERT  INTO  @TableScript(ScriptLine)  VALUES(N')')     INSERT  INTO  @TableScript(ScriptLine)  VALUES(N'GO')     INSERT  INTO  @TableScript(ScriptLine)  VALUES(N'')    --获取表备注     DECLARE  @PropScript  nvarchar(4000)     INSERT  INTO  @TableScript(ScriptLine)       SELECT  N'EXEC  sys.sp_addextendedproperty  @name=N'''  +  a.Name  +  N''',  @value=N'''  +  CONVERT(nvarchar,  a.Value)  +  N'''  ,@level0type=N''SCHEMA'',  @level0name=N'''  +              USER_NAME(OBJECTPROPERTY(a.major_Id,  N'OwnerId'))  +  N''',  @level1type=N''TABLE'',  @level1name=N'''  +  b.Name  +  N''''         FROM  sys.extended_properties  a           LEFT  JOIN  sys.objects  b  ON  b.[Object_ID]  =  a.major_Id       WHERE  a.major_Id  =  @ObjectID         AND  Minor_ID  =  0     INSERT  INTO  @TableScript(ScriptLine)  VALUES('GO')       DECLARE  PropCursor  CURSOR  FOR        SELECT  N'EXEC  sys.sp_addextendedproperty  @name=N'''  +  a.Name  +  N''',  @value=N'''  +  CONVERT(nvarchar,  a.Value)  +  N'''  ,@level0type=N''SCHEMA'',  @level0name=N'''  +              USER_NAME(OBJECTPROPERTY(a.major_Id,  N'OwnerId'))  +  N''',  @level1type=N''TABLE'',  @level1name=N'''  +  b.Name  +  N''''  +              N',  @level2type=N''COLUMN'',  @level2name=N'''  +  c.[Name]  +  ''''         FROM  sys.extended_properties  a           LEFT  JOIN  sys.objects  b  ON  b.[Object_ID]  =  a.major_Id           LEFT  JOIN  sys.syscolumns  c  ON  c.[ID]  =  a.major_Id  AND  c.ColID  =  a.Minor_ID       WHERE  a.major_Id  =  @ObjectID         AND  Minor_ID  <>  0     OPEN  PropCursor     FETCH  NEXT  FROM  PropCursor  INTO  @PropScript     WHILE  @@FETCH_STATUS  =  0     BEGIN       INSERT  INTO  @TableScript(ScriptLine)  VALUES(@PropScript)       INSERT  INTO  @TableScript(ScriptLine)  VALUES(N'GO')         FETCH  NEXT  FROM  PropCursor  INTO  @PropScript     END     CLOSE  PropCursor     DEALLOCATE  PropCursor     INSERT  INTO  @TableScript(ScriptLine)  VALUES('')       --获取表外键     DECLARE  @ConstID  int,  @i  tinyint,  @keyCnt  tinyint,  @TempletSQL  nvarchar(400),  @SQLScript  nvarchar(500),             @FColName  sysname,  @RColName  sysname,             @ForeignLine  nvarchar(4000),  @ReferencesLine  nvarchar(4000),  @ReferencesAction  nvarchar(4000)     DECLARE  @ConstIDTable  table(ConstID  int)     SELECT       @FColName  =  '',       @RColName  =  '',       @TempletSQL  =          N'SELECT  @eFColName  =  ''[''  +  col_name(FkeyID,  Fkey%d)  +  '']'',  @eRColName  =  ''[''  +  col_name(RkeyID,  Rkey%d)  +  '']''  FROM  sys.sysreferences  WHERE  ConstID  =  @ConstID'       INSERT  INTO  @ConstIDTable       SELECT  ConstID  FROM  sys.sysreferences  WHERE  FKeyID  =  @ObjectID  OR  RKeyID  =  @ObjectID  ORDER  BY  FKeyID       WHILE  EXISTS(SELECT  *  FROM  @ConstIDTable)     BEGIN       SELECT  TOP  1  @ConstID  =  ConstID  FROM  @ConstIDTable       DELETE  FROM  @ConstIDTable  WHERE  ConstID  =  @ConstID         INSERT  INTO  @TableScript         SELECT  N'--ALTER  TABLE  [dbo].['  +  object_name(FKeyID)  +  ']  WITH  CHECK'           FROM  sys.sysreferences         WHERE  ConstID  =  @ConstID       INSERT  INTO  @TableScript(ScriptLine)  VALUES('--   ADD'  +  CHAR(13)  +  CHAR(10))         SELECT  @ForeignLine  =  N'--     CONSTRAINT  ['  +  object_name(ConstID)  +  ']  FOREIGN  KEY(',               @ReferencesLine  =  N'REFERENCES  [dbo].['  +  object_name(RKeyID)  +  ']  (',               @ReferencesAction  =  CASE  b.Delete_Referential_Action  WHEN  0  THEN  N''                                                                    WHEN  1  THEN  N'ON  DELETE  Cascade'                                                                   WHEN  2  THEN  N'ON  DELETE  SET  NULL'                                                                   WHEN  3  THEN  N'ON  DELETE  SET  DEFAULT'                                  END  +  '  '  +                                  CASE  b.Delete_Referential_Action  WHEN  0  THEN  N''                                                                    WHEN  1  THEN  N'ON  UPDATE  Cascade'                                                                   WHEN  2  THEN  N'ON  UPDATE  SET  NULL'                                                                   WHEN  3  THEN  N'ON  UPDATE  SET  DEFAULT'                                  END,              @keyCnt  =  KeyCnt          FROM  sys.sysreferences  a           LEFT  JOIN  sys.foreign_keys  b  ON  a.ConstID  =  b.Object_ID       WHERE  a.ConstID  =  @ConstID             --取字段       SET  @i  =  1       WHILE  @i  <=  @keyCnt       BEGIN         SET  @SQLScript  =  REPLACE(@TempletSQL,  '%d',  CONVERT(nvarchar,  @i))         EXEC  sp_executesql  @stmt  =  @SQLScript,  @params=  N'@eFColName  sysname  output,  @eRColName  sysname  output,  @ConstID  int',                             @eFColName  =  @FColName  output,  @eRColName  =  @RColName  output,  @ConstID  =  @ConstID         print  @SQLScript         SET  @ForeignLine  =  @ForeignLine  +  CASE  WHEN  @i  >  1  THEN  ',  '  ELSE  ''  END  +  @FColName         SET  @ReferencesLine  =  @ReferencesLine  +  CASE  WHEN  @i  >  1  THEN  ',  '  ELSE  ''  END  +  @RColName         SET  @i  =  @i  +  1       END         INSERT  INTO  @TableScript(ScriptLine)  VALUES(@ForeignLine  +  N')  '  +  @ReferencesLine  +  N')')       IF  @ReferencesAction  <>  ''         INSERT  INTO  @TableScript(ScriptLine)  VALUES(@ReferencesAction)       --INSERT  INTO  @TableScript(ScriptLine)  VALUES(N'GO')     END     --返回表的创建脚本     SELECT  ScriptLine  FROM  @TableScript     SET  NOCOUNT  OFF GO--测试USE LibrarySystemEXEC sp_HelpTable 'BookInfoList'对于查看视图,存储过程等系统有现成的.sp_helptext ---表结构SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOcreate proc [dbo].[sp_table] (@tableName varchar(200) ,@ColumnLike varchar(200)=NULL)      as      --/********************************************          --根据表名得到表信息，包括字段说明           --*********************************************/      --DECLARE @tableName VARCHAR(200); --DECLARE @ColumnLike VARCHAR(200); --SET @tableName='purchase' --SET @ColumnLike=NULL;  --如果表明不存在，就直接选出相似表  if not exists( select 1 from sysobjects where id = object_id(@tableName) and type = 'U')  begin  select name from sysobjects where name like '%'+@tableName + '%' and type = 'U'   return  end     --筛选相似列明  if(@ColumnLike is null)   set @ColumnLike = ''   declare @ColumnTable table(cName varchar(200))   insert @ColumnTable(cName)   select a.name from syscolumns a,sysobjects d   where a.id=d.id   and d.name = @tableName and a.name like '%'+ @ColumnLike +'%'      --查询表结构信息        SELECT         表名=case  when  a.colorder=1  then  d.name  else  ''  end,        表说明=case  when  a.colorder=1  then  isnull(f.value,'')  else  ''  end,        字段序号=a.colorder,        字段名=a.name,      字段说明=isnull(g.[value],''),    标识=case  when  COLUMNPROPERTY(  a.id,a.name,'IsIdentity')=1  then  '√'else  ''  end,        主键=case  when  exists(SELECT  1  FROM  sysobjects  where  xtype='PK'  and  parent_obj=a.id  and  name  in  (        SELECT  name  FROM  sysindexes  WHERE  indid  in(        SELECT  indid  FROM  sysindexkeys  WHERE  id  =  a.id  AND  colid=a.colid        )))  then  '√'  else  ''  end,        类型=b.name,        占用字节数=a.length,        长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),        小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),        允许空=case  when  a.isnullable=1  then  '√'else  ''  end,        默认值=isnull(e.text,'')               FROM  syscolumns  a        left  join  systypes  b  on  a.xusertype=b.xusertype        inner  join  sysobjects  d  on  a.id=d.id   and  d.xtype='U'  and   d.name<>'dtproperties'        left  join  syscomments  e  on  a.cdefault=e.id        left  join  sys.extended_properties  g  on  a.id=g.major_id  and  a.colid=g.minor_id          left  join  sys.extended_properties  f  on  d.id=f.major_id  and  f.minor_id=0        --where  d.name='要查询的表'     --如果只查询指定表,加上此条件       where d.name = @tableName     and exists(select 1 from @ColumnTable where cname = a.name)      order  by  a.id,a.colorder     GO  ---查看函數,存儲過程代碼 create procedure dusp_helptext @objname nvarchar(776) ,@columnname sysname = NULL as  set nocount on  declare @dbname sysname ,@objid int ,@BlankSpaceAdded  int ,@BasePos    int ,@CurrentPos  int ,@TextLength  int ,@LineId    int ,@AddOnLen   int ,@LFCR     int --lengths of line feed carriage return ,@DefinedLength int  /* NOTE: Length of @SyscomText is 4000 to replace the length of ** text column in syscomments. ** lengths on @Line, #CommentText Text column and ** value for @DefinedLength are all 255. These need to all have ** the same values. 255 was selected in order for the max length ** display using down level clients */ ,@SyscomText nvarchar(4000) ,@Line     nvarchar(255)  select @DefinedLength = 255 select @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines. Note Len function ignores                trailing blank spaces*/ CREATE TABLE #CommentText (LineId int  ,Text nvarchar(255) collate database_default)  /* ** Make sure the @objname is local to the current database. */ select @dbname = parsename(@objname,3) if @dbname is null  select @dbname = db_name() else if @dbname <> db_name()     begin         raiserror(15250,-1,-1)         return (1)     end  /* ** See if @objname exists. */ select @objid = object_id(@objname) if (@objid is null)     begin  raiserror(15009,-1,-1,@objname,@dbname)  return (1)     end  -- If second parameter was given. if ( @columnname is not null)   begin     -- Check if it is a table     if (select count(*) from sys.objects where object_id = @objid and type in ('S ','U ','TF'))=0       begin         raiserror(15218,-1,-1,@objname)         return(1)       end     -- check if it is a correct column name     if ((select 'count'=count(*) from sys.columns where name = @columnname and object_id = @objid) =0)       begin         raiserror(15645,-1,-1,@columnname)         return(1)       end   if (ColumnProperty(@objid, @columnname, 'IsComputed') = 0)  begin   raiserror(15646,-1,-1,@columnname)   return(1)  end      declare ms_crs_syscom CURSOR LOCAL     FOR select text from syscomments where id = @objid and encrypted = 0 and number =             (select column_id from sys.columns where name = @columnname and object_id = @objid)             order by number,colid     FOR READ ONLY    end else if @objid < 0 -- Handle system-objects  begin  -- Check count of rows with text data  if (select count(*) from master.sys.syscomments where id = @objid and text is not null) = 0   begin   raiserror(15197,-1,-1,@objname)   return (1)   end     declare ms_crs_syscom CURSOR LOCAL FOR select text from master.sys.syscomments where id = @objid   ORDER BY number, colid FOR READ ONLY  end else   begin   /*     ** Find out how many lines of text are coming back,     ** and return if there are none.     */     if (select count(*) from syscomments c, sysobjects o where o.xtype not in ('S', 'U')       and o.id = c.id and o.id = @objid) = 0         begin             raiserror(15197,-1,-1,@objname)             return (1)         end      if (select count(*) from syscomments where id = @objid and encrypted = 0) = 0         begin             raiserror(15471,-1,-1,@objname)             return (0)         end   declare ms_crs_syscom CURSOR LOCAL  FOR select text from syscomments where id = @objid and encrypted = 0   ORDER BY number, colid  FOR READ ONLY    end  /* ** else get the text. */ select @LFCR = 2 select @LineId = 1   OPEN ms_crs_syscom  FETCH NEXT from ms_crs_syscom into @SyscomText  WHILE @@fetch_status >= 0 begin    select @BasePos  = 1  select @CurrentPos = 1   select @TextLength = LEN(@SyscomText)    WHILE @CurrentPos != 0   begin     --Looking for end of line followed by carriage return     select @CurrentPos =  CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)      --If carriage return found     IF @CurrentPos != 0     begin       /*If new value for @Lines length will be > then the       **set length then insert current contents of @line       **and proceed.       */       while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength       begin         select @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded)         INSERT #CommentText VALUES         ( @LineId,          isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))         select @Line = NULL, @LineId = @LineId + 1,             @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0       end       select @Line  = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')       select @BasePos = @CurrentPos+2       INSERT #CommentText VALUES( @LineId, @Line )       select @LineId = @LineId + 1       select @Line = NULL     end     else     --else carriage return not found     begin       IF @BasePos <= @TextLength       begin         /*If new value for @Lines length will be > then the         **defined length         */         while (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength         begin           select @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded)           INSERT #CommentText VALUES           ( @LineId,            isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N''))           select @Line = NULL, @LineId = @LineId + 1,             @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0         end         select @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')         if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0         begin           select @Line = @Line + ' ', @BlankSpaceAdded = 1         end       end     end   end   FETCH NEXT from ms_crs_syscom into @SyscomText end  IF @Line is NOT NULL   INSERT #CommentText VALUES( @LineId, @Line )  select Text from #CommentText order by LineId  CLOSE ms_crs_syscom DEALLOCATE ms_crs_syscom  DROP TABLE #CommentText  return (0) -- sp_helptext 

原标题：sql: sq_helptext

关键词：sql