读取数据库中表的结构信息

/*********************************************
读取数据库中表的结构信息
*********************************************/
CREATE Procedure GetAllTableInfo As
begin

DECLARE @strName nvarchar(128), @Tid int,@strOwner varchar(50) --,@au_id varchar(11)
set nocount on
--获取所有的用户表信息

DECLARE All_Tables CURSOR
FOR select convert(nvarchar(128),Name),ID from sysobjects where OBJECTPROPERTY(id, N'IsUserTable')=1 and OBJECTPROPERTY(id, N'IsMSShipped')=0 order by Name

Create Table #ColInfo
(
      TName nvarchar(50),           --表名称
      ColName nvarchar(50),         --列名称
      ColID smallint,              --列ID
      REMARK nvarchar(1000),       --注释
      Type varchar(50),            --数据类型
      DefaultValue nvarchar(255),   --缺省值
      IsIden nchar(1),             --是否标识列
      IsAllowNull nchar(1),        --是否允许为空
      IsPrimaryKey nchar(1)        --是否为主键
)

--主键信息
Create Table #PkInfo
(
      TABLE_QUALIFIER nvarchar(50),
      TABLE_OWNER varchar(50),
      TABLE_NAME nvarchar(50),
      COLUMN_NAME nvarchar(50),
      KEY_SEQ int,
      PK_NAME nvarchar(50)
)

set @strOwner=user_name()

OPEN All_Tables
FETCH NEXT FROM All_Tables into @strName,@Tid
while @@FETCH_STATUS = 0
begin
      --插入表及其注释信息
      Insert Into #ColInfo
      SELECT @StrName,'',0,isnull(convert(nvarchar(1000),Value),''),'','','','',''
      FROM (select @strName TName) A left join
             ::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, NULL, NULL) --表的注释信息
            ON 1=1
      --插入列信息
      insert into #ColInfo
      select @strName,C.name CName,c.colid,isnull(convert(nvarchar(1000),R.value),''),
             (case when (C.xtype>=34 and C.xtype<=58) or C.xtype in (61,98,99,104,127,189) then T.name
     when c.xtype in (59,60,106,108,122) then T.name+'('+convert(varchar,C.xprec)+','+convert(varchar,C.xscale)+')'
   else T.name+'('+convert(varchar,c.prec)+'/'+convert(varchar,C.length)+')' end),
             isnull(Def.text,''),
             (case c.status when 0x80 then '√' else '' end),
             case isnullable when 1 then '是' else '否' end,
             ''
      from (systypes T
           inner join
            ( syscolumns C
                left join SysComments Def
                on C.cdefault=Def.id and 1=Def.colid
            ) on T.xtype=C.xtype and T.xusertype=C.xusertype
           )
           left join
             ::fn_listextendedproperty('MS_Description', N'USER', N'dbo', N'Table', @strName, N'Column', NULL) R --列的注释信息
           on convert(varchar,R.objname)=convert(varchar,C.Name)
      where C.id=object_id(@strName)
order by c.colid

insert into #PkInfo EXEC [dbo].[sp_pkeys] @strName,@strOwner, DEFAULT

fetch next from All_Tables into @strName,@Tid
end

--更新主键信息
update #ColInfo
set IsPrimaryKey='*'
from #PkInfo
where #PkInfo.TABLE_NAME=#ColInfo.TName and #PkInfo.COLUMN_NAME=#ColInfo.ColName

Close All_Tables
DEALLOCATE All_Tables

select '☆***☆' ObjectName,'' REMARK,'' Col1,'' Col2,'' Col3,'' Col4,'' Col5,TName TableName,0 flag,colid
from #ColInfo
where ColName=''

union
select '表名：'+TName,REMARK,'','','','','',TName,1 flag,colid
from #ColInfo
where ColName=''

union
select '列名','注释','数据类型','缺省值','标识','允许空','主键',TName,2,colid
from #ColInfo
where ColName=''

union
select ColName,REMARK,Type,DefaultValue,IsIden,IsAllowNull,IsPrimaryKey,TName,3,colid
from #ColInfo
where ColName<>''

order by 8,9,10

drop table #PkInfo
drop table #ColInfo

end
GO

/*********************************************
读取数据库中表的结构信息
*********************************************/
CREATE Procedure GetTableInfo @tableName nvarchar(128) As
begin

DECLARE @strName nvarchar(128), @Tid int,@strOwner varchar(50) --,@au_id varchar(11)

set nocount on

--获取所有的用户表信息

DECLARE All_Tables CURSOR
FOR select convert(nvarchar(128),Name),ID from sysobjects where OBJECTPROPERTY(id, N'IsUserTable')=1 and OBJECTPROPERTY(id, N'IsMSShipped')=0 and object_name(id)=@tableName order by Name