Access开发培训
网站公告
·Access专家课堂QQ群号:151711184    ·Access快速开发平台下载地址及教程    ·欢迎加入Access专家课堂微信群!    ·如何快速搜索本站文章|示例|资料    
您的位置: 首页 > 技术文章 > ADP及SQL SERVER

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

时 间:2012-06-11 11:13:20
作 者:赵文斌   ID:4506  城市:深圳
摘 要:SQL SERVER2005资料收集
正 文:

用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



Access软件网QQ交流群 (群号:54525238)       Access源码网店

常见问答:

技术分类:

相关资源:

专栏作家

关于我们 | 服务条款 | 在线投稿 | 友情链接 | 网站统计 | 网站帮助