柳永法常用SQL语句备忘

--------------------------数据库操作--------------------------  
--建数据库  
create database yongfa365_com  
on 
( name = yongfa365_comt,  
  filename = 'd:\yongfa365_com.mdf',  
  size = 4,  
  maxsize = 10,  
  filegrowth = 1  
)  
 
 
--删数据库  
drop database yongfa365_com  
 
--备份数据库  
backup database yongfa365_com to disk='d:\yongfa365_com.bak' 
 
--批量收缩所有数据库  
declare cur cursor for select name from Master..SysDatabases where name not in ('master','model','msdb','Northwind','pubs')  
declare @tb sysname   
 
open cur   
fetch next from cur into @tb   
while @@fetch_status=0   
begin   
 exec ('dump transaction ['+@tb+'] with no_log')   
 exec ('backup log ['+@tb+'] with no_log')   
 exec ('dbcc shrinkdatabase(['+@tb+'])')   
 fetch next from cur into @tb   
end   
close cur   
deallocate cur  
--------------------------数据库操作--------------------------  
 
 
----------------------------表操作----------------------------  
--删除表  
drop table movies  
 
--通用建表结构  
Create Table [dbo].[表名] (  
  [Id] int primary key identity(1,1),--ID，主键，自动号  
  [txtTitle] varchar(255),--标题  
  [txtContent] varchar(MAX),--内容  
  [AddTime] datetime Default (getdate()),--提交时间  
  [ModiTime] datetime Default (getdate()),--修改时间  
  [Hits] int Default (0),--点击数  
  [Flags] int Default (0) ,