鲁达 一、获取所有数据库
select * from ma order by name asc 二、获取某个库中所有表
SELECT * FROM SysObjects Where XType='U' Order by name asc--XType='U':表示所有用户表;--XType='S':表示所有系统表; 三、获取某表中的字段，字段类型等
--自然排序SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='ZL_User' --字段名排序SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='ZL_User' order by COLUMN_NAME ASC 四、按笔画拼音排序
--按笔画排序ALTER TABLE [ZL_User]ALTER COLUMN [UserName] nvarchar(100) COLLATE Chinese_PRC_Stroke_CI_ASselect * from [ZL_User] order by [UserName] --按拼音排序 ALTER TABLE [ZL_User]ALTER COLUMN [UserName] nvarchar(100) COLLATE Chinese_PRC_CI_ASselect *from [ZL_User] order by [UserName] 五、查询全库表和字段，并包括注释等细节
SELECT库名= case when a.colorder=1 then h.table_catalog else '' end,模式= case when a.colorder=1 then h.table_schema else '' end,表名= case when a.colorder=1 then d.name else '' end,表说明= case when a.colorder=1 then isnull;') else '' end,字段序号= a.colorder,字段名= a.name,标识= case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,主键= case when 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 ))) then '√' else '' end,类型= b.name,占用字节数= a.length,长度= COLUMNPROPERTY;PRECISION'),小数位数= isnull(COLUMNPROPERTY;Scale'),0),允许空= case when a.isnullable=1 then '√'else '' end,默认值= isnull;'),字段说明= isnull(g.[value],'')FROM syscolumns aleft join systypes b on a.xusertype=b.xusertypeinner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'left join syscomments e on a.cdefault=e.idleft join g on a.id=g.major_id and a.colid=g.minor_idleft join f on d.id=f.major_id and f.minor_id=0left join in h on d.name = h.table_name--where d.name='表名' --如果只查询指定表,加上此条件order by a.id,a.colorder