SQL获取数据库、表、视图、字段、结构

--得到所有的数据库

select name from sysdatabases 

 

--得到所有的表

SELECT name FROM sysobjects WHERE xtype='u' order by name

 

--得到所有的视图

select name from sys.views order by name

 

--得到表的字段

SELECT a.name as f_name, b.value from 
sys.syscolumns a LEFT JOIN sys.extended_properties b on a.id=b.major_id AND a.colid=b.minor_id AND b.name='MS_Description' 
WHERE object_id('users')=a.id ORDER BY a.colid

 

--得到表的结构

select a.name as f_name,b.name as t_name,[length],a.isnullable as is_null, a.cdefault as cdefault,COLUMNPROPERTY( OBJECT_ID('{0}'),a.name,'IsIdentity') as isidentity from 
sys.syscolumns a inner join sys.types b on b.user_type_id=a.xtype 
where object_id('{0}')=id order by a.colid