SQL Server 常用语句

/*
更改列名
*/
exec sp_rename 'WebSyncTest.CreateTimev','CreateTime'


--删除所有记录
truncate table tableName

/*
清除日志
*/
select log_reuse_wait_desc from sys.databases where name = 'lylearnnet_syn'
select * from sys.databases where name = 'lylearnnet_syn'
select name, log_reuse_wait_desc from sys.databases;
--截断
BACKUP LOG lylearnnet_syn WITH NO_LOG   
--清空
DUMP TRANSACTION lylearnnet WITH NO_LOG
DUMP TRANSACTION tbfd WITH NO_LOG


--just like oracle's desc
sp_MShelpcolumns ' t_sync_rec'


--sql server 中 drop table if exits 的实现：
if exists (select 1 from dbo.sysobjects where id=object_id(N't_subject_hy') and OBJECTPROPERTY(id,N'IsUserTable')=1) 
drop table t_subject_hy; 


/*
标识符identity
*/
--用法
create table table_name (
	id int IDENTITY(1,1) NOT NULL,
	...
)
--identity列显式赋值
SET IDENTITY_Insert  WebSyncTest ON   
insert into  WebSyncTest(id,name) values('abc','admin')   
SET IDENTITY_Insert  WebSyncTest OFF 


/*
表复制
*/
--只复制表结构
select * into b from a where 1<>1


--外键关联
alter table CardAccountTest add CONSTRAINT FK_CardAccount_WebClass_1 FOREIGN KEY (classId) REFERENCES WebClass(classId) ON DELETE CASCADE ON UPDATE CASCADE


--为时间字段赋默认值（建立该字段时未指定默认值）
ALTER TABLE WebUser ADD CONSTRAINT DF_WebUser_CreateTime DEFAULT(GETDATE()) FOR CreateTime


/*
分页
*/
--方案一（只适用于2005）
SELECT TOP 页大小 * 
FROM 
	(
	SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
	) A
WHERE RowNumber>页大小*(页数-1)
其他方案见我的博客