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
