SqlServer 常用T-SQL语法
--查看对象的T-SQL
exec sp_helptext procName
--查看性能
SET STATISTICS profile ON
SET STATISTICS io ON
SET STATISTICS time ON
--执行计划
go
set showplan_all on
go
exec Proc_BMIS_Target_RedAndGreenLamp '11020101',2010,5
go
set showplan_all off
--创建触发器
ALTER TRIGGER [dbo].[TRIGGER_TARGET_MONTH]
on [dbo].[Target_Month]
for INSERT,UPDATE,DELETE
AS
BEGIN
--
END
--创建索引
CREATE INDEX索引名 ON 表(字段) -----------------------------------------不唯一，非聚焦索引
CREATE UNIQUE CLUSTERED INDEX 索引名 ON表(字段)-----------------唯一聚焦索引
--创建游标
-- =============================================
-- Author: Zehui Shu
-- Create date: 2010-12-23
-- Description: 通过游标实现同步表数据
-- =============================================
CREATE PROCEDURE [dbo].[Proc_Syn_Data]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ID INT;
TRUNCATE TABLE Table2;
-- 1.声明游标
DECLARE CUR_MonthID CURSOR FOR
SELECT ID FROM [Table1]
-- 2.打开游标
OPEN CUR_MonthID
-- 3.从一个游标中查找信息，实现自己的数据处理。
FETCH CUR_MonthID INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO [Table2]
SELECT * FROM [Table1] tm WHERE tm.ID=@ID
FETCH NEXT FROM CUR_MonthID INTO @ID
END;
-- 4.关闭游标
CLOSE CUR_MonthID;
-- 5.释放游标
DEALLOCATE CUR_MonthID;
END
/***********建约束{共五类，注意字段名的括号[只有默认约束不要括号]}***********/
--CHECK约束ck
ALTER TABLE <表名> ADD CONSTRAINT <约束名> CHECK ([字段名]>0 AND [字段名]<100)
--PRIMARY KEY约束pk
ALTER TABLE <表名> ADD CONSTRAINT [约束名] PRIMARY KEY([字段名])
--FOREIGN KEY约束fk
ALTER TABLE <外键表名> ADD CONSTRAINT [约束名] FOREIGN KEY ([外键字段名]) REFERENCES <主键表名> ([主键字段名])
--UNIQUE约束uni
ALTER TABLE <表名> ADD CONSTRAINT [约束名] UNIQUE ([字段名])
--DEFAULT约束dft
ALTER TABLE <表名> ADD CONSTRAINT [约束名] DEFAULT '默认值' FOR [字段名]
--检查约束，删除约束
IF EXISTS(SELECT * FROM sysobjects s WHERE s.[name]='PK_Target_KPIInfo')
ALTER TABLE Target_Month DROP CONSTRAINT [约束名]
/*************批量插入数据************/
--方法1
INSERT INTO DATA_UFO_TBL_20101214_bak(字段列表) SELECT 字段列表 FROM DATA_UFO_TBL_20101214_bak WHERE bbny='201007' AND dwbh='11020101'
--方法2NewTable是将要创建出的新表，这种方法可用于备份数据表
SELECT * INTO NewTable FROM DATA_UFO_TBL_20101214_bak
/***************行转列****************/
SELECT * FROM dbo.Target_Month a
PIVOT
(
SUM(DATA)
FOR KPICodeID IN ([1],[2],[3],[4],[5])
) b
/*************定时释放锁************
* 系统中，常常出现用户被其他用户锁住，系统不能正常运行的情况。
* 在这种情况下，需要手工将有锁的进程取消掉。
* 此功能将手工更改为自动运行。
*
* 在数据库系统中，作为任务，每1分钟运行一次。
*
* 取消进程的条件：
* 1、锁住了其他进程。
* 2、自身没有被其他进程锁住。
* 3、此进程已经空闲了2分钟以上。
*
*/
DECLARE
@USER_ID INT,
@KILLCMD VARCHAR(100),
@WAIT_TIME INT
-- 设置空闲时间
SET @WAIT_TIME = 2;
-- 根据选择条件，选择出应该取消的进程
DECLARE cur_lock CURSOR FOR
SELECT spid FROM master..sysprocesses
WHERE DATEDIFF(minute, last_batch, getdate()) > @WAIT_TIME
AND blocked = 0
AND spid IN(SELECT blocked FROM master..sysprocesses);
-- 取消所有选择出的进程。
OPEN cur_lock;
FETCH NEXT FROM cur_lock INTO @USER_ID;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @KILLCMD = 'kill ' + CAST(@USER_ID AS VARCHAR);
EXECUTE (@KILLCMD);
FETCH NEXT FROM cur_lock INTO @USER_ID;
END;
CLOSE cur_lock;
DEALLOCATE cur_lock;
/*************查看锁定信息************/
/****** 对象: StoredProcedure [dbo].[sp_who_lock] 脚本日期: 12/23/2010 12:52:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_who_lock]
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end
/*************配置数据库日志和收缩数据库************/
ALTER PROC [dbo].[Proc_DBCC_1231]
as
BEGIN
declare @dbname sysname
set @dbname='BMIS_DW'
--1.清空日志
exec('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG')
--2.截断事务日志：
exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec('DBCC SHRINKDATABASE(['+@dbname+'])')
--4.设置自动收缩
exec('EXEC sp_dboption '''+@dbname+''',''autoshrink'',''TRUE''')
set @dbname='BMISDEV'
--1.清空日志
exec('DUMP TRANSACTION ['+@dbname+'] WITH NO_LOG')
--2.截断事务日志：
exec('BACKUP LOG ['+@dbname+'] WITH NO_LOG')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec('DBCC SHRINKDATABASE(['+@dbname+'])')
--4.设置自动收缩
exec('EXEC sp_dboption '''+@dbname+''',''autoshrink'',''TRUE''')
END
/*************备份还原数据库************/
--还原数据库
RESTORE DATABASE DB_Name
FROM DISK = 'E:\APJ.Project\DB_Name\DataBase_Back\客户数据库20110328\TestDB20110326'
with replace,
MOVE 'DB_Name' TO 'D:\database\DB_Name.mdf',
MOVE 'DB_Name_log' TO 'D:\database\DB_Name_log.ldf'
--完整备份
BACKUP DATABASE dbName
TO DISK = 'D:\SQLDATA\Black\TestR2.bat'
WITH FORMAT
--差异备份
BACKUP DATABASE dbName
TO DISK = 'D:\SQLDATA\Black\TestR2.bat'
WITH DIFFERENTIAL