你的位置:首页 > 数据库

[数据库]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=  [email protected]  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 [email protected] + '%' 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 + @[email protected] + @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, @[email protected] + @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 + @[email protected]+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, @[email protected]+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 

  

---2005附加數據庫---ATTACH DATABASE TEMPLATEexec sp_attach_db 'Asset5','D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Asset5.mdf','D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Asset5_log.ldf'GO---列出存儲過程exec sp_stored_proceduresGO--系統視圖select * from sys.objects---列出存儲過程select * from sys.objects WHERE TYPE='P'select [name] from sysobjects where xtype='P' order by [name]GO---列出所有表select * from sys.objects WHERE TYPE='U' order by [name]select [name] from sysobjects where xtype='U' order by [name]GO--列出視圖select * from sys.objects WHERE TYPE='V' order by [name]select [name] from sysobjects where xtype='V' order by [name]GO--select * from sysobjectsGO--列出所有表select [name] from sysobjects where xtype='u' order by [name]GO/* select name from sysobjects where xtype='u' ---  C = CHECK 约束  D = 默认值或 DEFAULT 约束  F = FOREIGN KEY 约束  L = 日志  FN = 标量函数  IF = 内嵌表函数  P = 存储过程  PK = PRIMARY KEY 约束(类型是 K)  RF = 复制筛选存储过程  S = 系统表  TF = 表函数  TR = 触发器  U = 用户表  UQ = UNIQUE 约束(类型是 K)  V = 视图  X = 扩展存储过程  */  --得到数据库存储过程列表:select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name--得到某个存储过程的参数信息:(SQL方法) proc_Insert_BookAdministratorListOutputselect * from syscolumns where ID in   (SELECT id FROM sysobjects as a   WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1    and id = object_id(N'[dbo].[proc_Insert_BookAdministratorListOutput]'))--得到数据库所有表:select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name---得到某个表中的字段信息:dbo.BookInfoListselect c.name as ColumnName, c.colorder as ColumnOrder, c.xtype as DataType, typ.name as DataTypeName, c.Length, c.isnullable from dbo.syscolumns c inner join dbo.sysobjects t on c.id = t.id inner join dbo.systypes typ on typ.xtype = c.xtypewhere OBJECTPROPERTY(t.id, N'IsUserTable') = 1 and t.name='BookInfoList' order by c.colorder;select a.name,b.name,a.length,a.isnullable from syscolumns a,systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.xtype='U' and a.id =object_id('BookInfoList')--得到存储过程内容EXEC Sp_HelpText '[proc_Insert_BookAdministratorListOutput]'--得到视图View定义:dbo.View_BookInfoList--获取View, Procedure, Trigger, Function的源代码EXEC Sp_HelpText 'View_BookInfoList'--EXEC Sp_HelpText 'BookInfoList'SELECT b.name, a.name AS Expr1, a.id, a.xtype, a.typestat, a.xusertype, a.length, a.xprec, a.xscale, a.colid, a.xoffset, a.bitpos, a.reserved, a.colstat, a.cdefault, a.domain, a.number, a.colorder, a.autoval, a.offset, a.collationid, a.language, a.status, a.type, a.usertype, a.printfmt, a.prec, a.scale, a.iscomputed, a.isoutparam, a.isnullable, a.collation, a.tdscollationFROM syscolumns AS a INNER JOINsysobjects AS b ON b.id = a.idWHERE (b.xtype = 'U') AND (b.name <> 'dtproperties')--得到数据库存储过程列表select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name--列出所有数据库SELECT name FROM sys.sysdatabases order by name asc  --查詢數據庫中的表所占用空間exec sp_spaceused '表名' --取得表占用空間 exec sp_spaceused ''--數據庫所有空間