在sql server中怎样用sql得到库中所有的表名,以及表的结构(列名和数据类型)

CREATE  PROCEDURE gettableinfo
/*@TableName varchar(32)*/
AS
   
/*创建临时表*/
create table #TableFields(
  tableName   varchar(32),
  fieldname   varchar(32),
  fieldtype   varchar(32),
  fieldlength varchar(32),
  scale       varchar(32),
  des         varchar(256),
  defaultvalue varchar(32),
  CanNULL varchar(32)
)
/* 声明游标*/
declare table_cur scroll cursor
for select sysobjects.name from sysobjects where sysobjects.xtype = 'U'
for update of sysobjects.name
/*声明临时表名*/
declare @TName varchar(32)
/* 打开游标*/
open table_cur
fetch next from table_cur into @TName
while @@fetch_status=0 begin
      SELECT sysobjects.name AS tableName, syscolumns.name AS filedname,
      systypes.name AS fieldtype, syscolumns.length,
      syscolumns.scale
   into #FiledInfo_Master
   FROM syscolumns INNER JOIN
      systypes ON syscolumns.xtype = systypes.xtype INNER JOIN
      sysobjects ON syscolumns.id = sysobjects.id
WHERE (sysobjects.xtype = 'U') AND (systypes.name <> 'sysname') and   sysobjects.name=@TName
     /*得到字段描述*/
     SELECT objname as filedname ,value   into  #FiledInfo
     FROM ::fn_listextendedproperty('MS_Description', 'user',
       'dbo', 'table', @TName,
      'column', DEFAULT)
      
      
/*得到字段缺省值*/
  SELECT objname  as filedname, value as defaultvalue
  into  #FiledInfo2
  FROM ::fn_listextendedproperty('DefaultValue', 'user',
       'dbo', 'table', @TName,
      'column', DEFAULT)
      
/*得到字段是否可为空*/
SELECT objname  as filedname, value as CanNULL
  into  #FiledInfo3
  FROM ::fn_listextendedproperty('MS_AllowBlanks', 'user',
       'dbo', 'table', @TName,
      'column', DEFAULT)
      
      
      
/*联结字段描述和属性*/      
insert into #TableFields
SELECT cast(#FiledInfo_Master.tableName as varchar(32)) ,
      cast(#FiledInfo_Master.filedname as  varchar(32)),
      cast(#FiledInfo_Master.fieldtype  as varchar(32)),
      cast(#FiledInfo_Master.length as  varchar(32)),
      cast(#FiledInfo_Master.scale as  varchar(32)),
      cast(#FiledInfo.[value] as  varchar(256)),
      cast(#FiledInfo2.defaultvalue  as varchar(32)),
      cast(#FiledInfo3.CanNULL  as  varchar(32))
      
FROM dbo.#FiledInfo_Master LEFT OUTER JOIN
      dbo.#FiledInfo ON dbo.#FiledInfo_Master.filedname = dbo.#FiledInfo.FiledName
     LEFT OUTER JOIN dbo.#FiledInfo2
       ON dbo.#FiledInfo_Master.filedname = dbo.#FiledInfo2.FiledName
LEFT OUTER JOIN dbo.#FiledInfo3
       ON dbo.#FiledInfo_Master.filedname = dbo.#FiledInfo3.FiledName
where
#FiledInfo_Master.tablename=@TName
fetch next from table_cur into @TName  
--if exists (select * from dbo.sysobjects where id = object_id(N'#FiledInfo_Master') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table #FiledInfo_Master
--if exists (select * from dbo.sysobjects where id = object_id(N'#FiledInfo') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table #FiledInfo
--if exists (select * from dbo.sysobjects where id = object_id(N'#FiledInfo2') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table #FiledInfo2
drop table #FiledInfo3      
end
select * from #TableFields
deallocate table_cur

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO