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
from sysobjects a,syscolumns b,systypes c
where a.id = b.id and b.xtype = c.xtype and a.name = ' 表名 '
order by b.colid
1
select
column_name,data_type ,character_maximum_length
2 from information_schema.columns
3 where table_name = ' 表名 '
4 order by ordinal_position
2 from information_schema.columns
3 where table_name = ' 表名 '
4 order by ordinal_position
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
2 From 表 Where ID in
3 ( Select Top M ID From 表)
4 Order by ID Desc
3、查询用户创建的所有数据库
1
select
*
from
master..sysdatabases D
2 where sid not in
3 ( select sid from master..syslogins where name = ' sa ' )
2 where sid not in
3 ( select sid from master..syslogins where name = ' sa ' )
4、查看当前数据库中所有存储过程
1
select
name
as
存储过程名称
2 from sysobjects
3 where xtype = ' P ' -- 视图为'V',触发器'TR',用户表为'U',系统表为'S'
2 from sysobjects
3 where xtype = ' P ' -- 视图为'V',触发器'TR',用户表为'U',系统表为'S'