mssql sqlserver 查询数据库引擎下 数据库、数据表、用户数据表、数据表结构的方法分享


摘要:
下文讲述sqlserver中使用sql脚本查询数据库引擎下的数据库 数据表 数据表结构的方法分享,如下所示:
实验环境:sql server 2008 R2

废话少说,直接给大家上干货,下文是根据sqlserver的系统表整理出的 获取表结构相关信息的sql脚本,如下所示:

 -- 查询当前数据库引擎下非系统数据库的列表
select name as [数据库名称] FROM Master.. SysDatabases where dbid>4

-- 查询数据库“maomao”下的所有数据表
use [maomao] 
go
SELECT name as [数据表名称] FROM sysobjects WHERE xtype = 'U' Or xtype = 'S'

--查询数据库“maomao”下的所有用户表
use [maomao] 
go
SELECT name  as [数据表名称] FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0

-- 查询数据库“maomao”下的test 表的字段名,长度,类型,字段说明
use [maomao] 
go
SELECT a.[name] as '字段名称',
       a.length '字段长度',
	   c.[name] '字段类型',
	   e.value as '字段备注及说明' 
 FROM syscolumns  a 
left   join    systypes    b   on      a.xusertype=b.xusertype 
left     join     systypes     c     on      a.xtype = c.xusertype 
inner   join   sysobjects  d   on      a.id=d.id     and   d.xtype='U' 
left join sys.extended_properties e on a.id = e.major_id and a.colid = e.minor_id and e.name='MS_Description'
where d.name='test'


-- 查询数据库“maomao”下的所有数据表的字段名,长度,类型,字段说明
use [maomao] 
go
SELECT d.[name]    as [数据表名称],
       a.[name] as '字段名称',
	   a.length '字段长度',
	   c.[name] '字段类型',
	   e.value as '字段备注及说明' 
FROM syscolumns  a 
left   join    systypes    b   on      a.xusertype=b.xusertype 
left     join     systypes     c     on      a.xtype = c.xusertype 
inner   join   sysobjects  d   on      a.id=d.id     and   d.xtype='U' 
left join sys.extended_properties e on a.id = e.major_id and a.colid = e.minor_id and e.name='MS_Description'