1231 sqlserver_SqlServer 常用T-SQL语法

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+'])')

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