SQL惯用的设置和函数(1)
SQL常用的设置和函数(1)
1.切换或打开数据库:USE 数据库名 2.调整数据库的大小: USE test GO ALTER DATABASE test MODIFY FILE ( NAME='test' FILENAME='D:\XP\SQLDB\test.mdf', SIZE=20MB )
3.查看目前数据库选项设定及修改: EXEC sp_dboption '数据库' '选项参数',True 选项参数: trunc. log on chkpt --防止日志增长太快 dbo use only --只有数据库拥有者才能使用数据库 read only --可以检索数据,但不能修改 single user --每次只能一个用户访问 offline --数据库为离线状态 autoshrink --数据库将自动周期性收缩大小 ANSI warnings --提示‘除0’之类的标准错误和警告 4.将数据库减缩的最小容量 USE 数据库 GO DBCC SHRINKDATABASE(数据库) 5.更改数据库名: EXEC sp_renamedb 'newname','oldname'
6.暂停控制命令 WAITFOR 用来暂时停止程序执行,知道设定时间已过货已到达才继续往下执行 WAITFOR { DELAY 'time' | TIME 'time'|ERROREXIT|PROCESSEXIT|MIRROREXIT } .DELAY:用来设定等待时间,最大24个小时 .TIME:用来设定等待结束的时间点 .ERROREXIT:直接处理非正常中断 .PEOCESSEXIT:直接处理正常或非正常中断 .MIRROREXIT:直到镜像设置失败
Examples A. Use WAITFOR TIME This example executes the stored procedure update_all_stats at 10:20 P.M. BEGIN WAITFOR TIME '22:20' EXECUTE update_all_stats END
B. Use WAITFOR DELAY This example shows how a local variable can be used with the WAITFOR DELAY option. A stored procedure is created to wait for a variable amount of time and then returns information to the user as to the number of hours, minutes, and seconds that have elapsed. CREATE PROCEDURE time_delay @@DELAYLENGTH char(9) AS DECLARE @@RETURNINFO varchar(255) BEGIN WAITFOR DELAY @@DELAYLENGTH SELECT @@RETURNINFO = 'A total time of ' + SUBSTRING(@@DELAYLENGTH, 1, 3) + ' hours, ' + SUBSTRING(@@DELAYLENGTH, 5, 2) + ' minutes, and ' + SUBSTRING(@@DELAYLENGTH, 8, 2) + ' seconds, ' + 'has elapsed! Your time is up.' PRINT @@RETURNINFO END GO -- This next statement executes the time_delay procedure. EXEC time_delay '000:00:10' GO Here is the result set: A total time of 000 hours, 00 minutes, and 10 seconds, has elapsed! Your time is up.