经典SQL语句--转载

1、列出数据库里所有的表名
select name from sysobjects where type='U'

2、新建一个与a表一样的空的b表
select * into b
from a where 1<>1

3、列出表里的所有的字段
select name from syscolumns where id=object_id('TableName')

4、说明：初始化表table1
TRUNCATE TABLE table1

5、说明：随机选择记录
select top 10 * from tablename order by newid()

6、跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

7、日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5

8、删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

9、日期算當月天數:
   select Day(dateadd(day,-1,convert(datetime,convert(char(07),dateadd(m,1,getdate()),120)+'-01')))
   select 32-Day(getdate()+(32-Day(getdate())))

10、按姓氏笔画排序
select * from 表名 order by 列名 Collate Chinese_PRC_Stroke_ci_as

 

11、按拼音首字母排序
select * from 表名 order by 列名 Collate Chinese_PRC_CS_AS_KS_WS

12、查看硬盘分区:
EXEC master..xp_fixeddrives

13、找出表中某一列相同的数据行
SELECT *
FROM 文章信息
WHERE (文章标题 IN
          (SELECT 文章标题
         FROM 文章信息
         GROUP BY 文章标题
         HAVING COUNT(*) > 1))

14、查找员工的编号、姓名、部门和出生日期，如果出生日期为空值，
--显示日期不详,并按部门排序输出,日期格式为yyyy-mm-dd。
select emp_no ,emp_name ,dept ,
       isnull(convert(char(10),birthday,120),'日期不详') birthday
from employee
order by dept

15、列出所有的用户数据表及其字段信息
SELECT TOP 100 PERCENT c.colid AS 序号, o.name AS 表名, c.name AS 列名,
      t.name AS 类型, c.length AS 长度, c.isnullable AS 允许空,
      CAST(m.[value] AS Varchar(100)) AS 说明
FROM syscolumns c INNER JOIN
      sysobjects o ON o.id = c.id AND objectproperty(o.id, N'IsUserTable') = 1 AND
      o.name <> 'dtproperties' INNER JOIN
      systypes t ON t.xusertype = c.xusertype LEFT OUTER JOIN
      sysproperties m ON m.id = o.id AND m.smallid = c.colorder
ORDER BY o.name, c.colid