用SQL从SQL SERVER中获取数据库结构信息

用SQL从SQL SERVER中获取数据库结构信息

一：功能

1：数据库名

2：数据库中表名

3：数据库中表结构（字段/主、外健）

4：数据库中VIEW

5：数据库中存储过程

二：SQL

1．获得数据库名： select name as dbase_name from sysfiles where fileid='1'

2． 表名： select name as tb_name from sysobjects where xtype='u' or xtype='pk' order by tb_name 

 3. 数据库中表结构（字段/主、外健）： select SysObjects.Name as tb_name, SysColumns.Name as col_name, SysTypes.Name as col_type, SysColumns.Length as col_len, isnull(SysProperties.Value,SysColumns.Name) as col_memo, case when SysColumns.name in (select 主键=a.name FROM syscolumns a inner join sysobjects b on a.id=b.id and b.xtype='U' and b.name<>'dtproperties' where exists(Select 1 FROM sysobjects where xtype='PK' and name in ( Select name FROM sysindexes Where indid in( Select indid FROM sysindexkeys Where id = a.id AND colid=a.colid ))) and b.name=SysObjects.Name ) then 1 else 0 end as is_key FROM SysObjects LEFT JOIN SysColumns ON Syscolumns.Id = Sysobjects.Id LEFT JOIN SysProperties ON (Syscolumns.Id = Sysproperties.Id AND Syscolumns.Colid = Sysproperties.Smallid) LEFT JOIN SysTypes ON SysTypes.Name <> 'sysname' and SysTypes.XType = Syscolumns.XType Where (Sysobjects.Xtype ='u' or Sysobjects.Xtype ='v') AND Sysobjects.Name Like 'web_do' orDER By SysObjects.Name, SysColumns.colid

4. 视图名：

 select name as view_name from sysobjects where xtype='v' order by view_name 

 5．存储过程：

 select name as sp_name from sysobjects where xtype='p' order by tb_name 

 三：整合部分

1. 把数据库名、表名、字段、key值、视图以一张表的形式表示：

 Select sysfiles.name as dbase_name,SysObjects.Name as tb_name, SysColumns.Name as col_name, SysTypes.Name as col_type, SysColumns.Length as col_len, isnull(SysProperties.Value,SysColumns.Name) as col_memo, case when SysColumns.name in (select 主键=a.name FROM syscolumns a inner join sysobjects b on a.id=b.id and b.xtype='U' and b.name<>'dtproperties' where exists(Select 1 FROM sysobjects where xtype='PK' and name in ( Select name FROM sysindexes Where indid in( Select indid FROM sysindexkeys Where id = a.id AND colid=a.colid ))) and b.name=SysObjects.Name ) then 1 else 0 end as is_key FROM SysColumns LEFT JOIN SysTypes on SysTypes.XType = Syscolumns.XType LEFT JOIN SysObjects on Sysobjects.Id = Syscolumns.Id LEFT JOIN sysfiles on SysObjects.userstat=sysfiles.groupid LEFT JOIN SysProperties ON (Syscolumns.Id = Sysproperties.Id AND Syscolumns.Colid = Sysproperties.Smallid) Where (Sysobjects.Xtype ='u' or Sysobjects.Xtype ='v') AND Sysobjects.Id = Syscolumns.Id AND SysTypes.XType = Syscolumns.XType AND SysTypes.Name <> 'sysname'and sysfiles.groupid=SysObjects.userstat AND Sysobjects.Name Like 'STORER' orDER By SysObjects.Name, SysColumns.colid 2．上面的SQL语句中dbase_name是要查表的数据库名，tb_name是要查的表名，col_name是所查表的字段名，col_type是所查表的字段类型，col_len是所查表的字段长度，is_key所查表的key值。把这些属性更形象的表示如下： dbase_name tb_name col_name col_type col_len is_key