SQL常用语句

1.数据库中按数据库大小排列。

select id,object_name(id) as tableName,indid,rows,rowcnt

from sysindexes where indid in(0,1) order by rows desc

2.分别获取当前年，月，日

SELECT YEAR(GETDATE()) AS Y,MONTH(GETDATE()) AS M,DAY(GETDATE()) AS D

 

3.显示数据库所有存储过程名称

select name as 存储过程名称 from sysobjects where xtype='P'

 

4.按姓氏笔画排序:

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多

 

5.获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0

6.获取某一个表的所有字段
select name from syscolumns where id=object_id('fcmess')

select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = 'fcmess')

两种方式的效果相同

7.查询某一个表的字段、数据类型和大小
select column_name,data_type,character_maximum_length from information_schema.columns
where table_name = 'fcmess'