查询SQLServer数据库信息的几条语句
摘自:http://hi.baidu.com/chinamis/blog/item/1a7423fa714a109258ee9071.html(迷茫深圳博客)
1、求某一表的字段名称,类型、长度:
select b.name as fieldname,c.name as typename,b.length as fieldlen
from sysobjects a,syscolumns b,systypes c
where a.id=b.id and b.xtype=c.xtype and a.name='表名'
order by b.colid
2、N到M条记录(要有主索引ID):
3、查询用户创建的所有数据库
4、查看当前数据库中所有存储过程
1、求某一表的字段名称,类型、长度:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1
select column_name,data_type ,character_maximum_length
2
from information_schema.columns
3
where table_name = '表名'
4
order by ordinal_position
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2、N到M条记录(要有主索引ID):
1
Select Top M-N *
2
From 表 Where ID in
3
(Select Top M ID From 表)
4
Order by ID Desc
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3、查询用户创建的所有数据库
1
select * from master..sysdatabases D
2
where sid not in
3
(select sid from master..syslogins where name='sa')
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4、查看当前数据库中所有存储过程
1
select name as 存储过程名称
2
from sysobjects
3
where xtype='P'--视图为'V',触发器'TR',用户表为'U',系统表为'S'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)