SQL
查询所有的触发器
SELECT
sys.[name] AS [trigger_name],
USER_NAME(sys.[uid]) AS [trigger_owner],
USER_NAME(sys2.[uid]) AS [table_schema],
OBJECT_NAME(sys.[parent_obj]) AS [table_name],
OBJECTPROPERTY(sys.[id], 'ExecIsUpdateTrigger') AS [isupdate],
OBJECTPROPERTY(sys.[id], 'ExecIsDeleteTrigger') AS [isdelete],
OBJECTPROPERTY(sys.[id], 'ExecIsInsertTrigger') AS [isinsert],
OBJECTPROPERTY(sys.[id], 'ExecIsAfterTrigger') AS [isafter],
OBJECTPROPERTY(sys.[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
OBJECTPROPERTY(sys.[id], 'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects AS sys
INNER JOIN sysobjects AS sys2 ON (sys.[parent_obj] = sys2.[Id])
WHERE sys.[type] = 'TR'
SQL游标循环读记录
带事务的游标循环 |
select
orderNum,userName,MoneyTotal
into
#t
from
pe_Orders po
DECLARE
@n
int
,@error
int
--set @n=1
set
@error=0
BEGIN
TRAN
--申明 开始事务
declare
@orderN
varchar
(50),@userN
varchar
(50)
--临时变量,用来保存游标值
declare
y_curr
cursor
for
--申明游标 为orderNum,userName
select
orderNum,userName
from
PE_Orders
where
Orderid<50
open
y_curr
fetch
next
from
y_curr
into
@orderN,@userN
while @@fetch_status = 0
BEGIN
select
isnull
(
sum
(MoneyTotal),0),orderNum
from
#t
where
username=@userN
-- set @n=@n+1
set
@error=@error+@@error
--记录每次运行sql后 是否正确 0正确
fetch
next
from
y_curr
into
@orderN,@userN
END
IF @error=0
BEGIN
commit
tran
---事务提交
END
ELSE
BEGIN
ROLLBACK
TRAN
---事务回滚
END
close
y_curr
deallocate
y_curr
DROP
TABLE
#t
正常循环语句
declare
@orderNum
varchar
(255)
create
table
#ttableName(id
int
identity(1,1),Orders
varchar
(255))
declare
@n
int
,@
rows
int
insert
#ttableName(orders)
select
orderNum
from
pe_Orders
where
orderId<50
--select @rows=count(1) from pe_Orders
select
@
rows
=@@rowcount
set
@n=1
while @n<=@
rows
begin
select
@orderNum=OrderNum
from
PE_Orders
where
OrderNum=(
select
Orders
from
#ttableName
where
id=@n)
print (@OrderNum)
select
@n=@n+1
end
drop
table
#ttableName
exists (返回结果集,为真)
not exists (不返回结果集,为真)
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.AID);
执行结果:
ID NAME
1 A1
2 A2
原理如下:
(1)SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1)
---> SELECT * FROM B WHERE B.AID=1有值,返回真,所以有数据
(2)SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
---> SELECT * FROM B WHERE B.AID=2有值,返回真,所以有数据
(3)SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
---> SELECT * FROM B WHERE B.AID=3无值,返回假,所以没有数据
得到的结果为,A.ID=1或2时才有数据,所以最终的条件等于
SELECT ID,NAME FROM A where id in (1,2);
1、修改字段名:
alter table 表名 rename column A to B
2、修改字段类型:
alter table 表名 alter column 字段名 type not null
3、修改字段默认值
alter table 表名 add default (0) for 字段名 with values
如果字段有默认值,则需要先删除字段的约束,在添加新的默认值,
select c.name from sysconstraints a
inner join syscolumns b on a.colid=b.colid
inner join sysobjects c on a.constid=c.id
where a.id=object_id('表名')
and b.name='字段名'
根据约束名称删除约束
alter table 表名 drop constraint 约束名
根据表名向字段中增加新的默认值
alter table 表名 add default (0) for 字段名 with values
4、增加字段:
alter table 表名 add 字段名 type not null default 0
5、删除字段:
alter table 表名 drop column 字段名;
select month(GETDATE()), year(GETDATE())
常用日期转换:
Select CONVERT(varchar(100), GETDATE(), 121): 2016-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2016-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 12): 160516
Select CONVERT(varchar(100), GETDATE(), 23): 2016-05-16