SQL代码备忘【来自网络】
Code
--断开某个库的所有连接
create proc [dbo].[p_killspid]
@dbname sysname --要断开连接的数据库名
as
declare @s nvarchar(1000)
declare tb cursor local for
select s='kill '+ cast(spid as varchar)
from master..sysprocesses
where dbid=db_id(@dbname)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
--断开某个库的所有连接
create proc [dbo].[p_killspid]
@dbname sysname --要断开连接的数据库名
as
declare @s nvarchar(1000)
declare tb cursor local for
select s='kill '+ cast(spid as varchar)
from master..sysprocesses
where dbid=db_id(@dbname)
open tb
fetch next from tb into @s
while @@fetch_status=0
begin
exec(@s)
fetch next from tb into @s
end
close tb
deallocate tb
Code
--获取某个表所有的列
SELECT * FROM sys.columns WHERE [object_id] = OBJECT_ID('AppSetting')
--获取当前表中所有的表
SELECT * FROM sys.tables ORDER BY [name]
--获取AppSetting表的ParamCode字段所有扩展属性
SELECT * FROM fn_listextendedproperty(default, 'user', 'dbo', 'table', 'AppSetting', 'column', 'ParamCode')
where objtype = 'COLUMN'
--获取某个表所有的列
SELECT * FROM sys.columns WHERE [object_id] = OBJECT_ID('AppSetting')
--获取当前表中所有的表
SELECT * FROM sys.tables ORDER BY [name]
--获取AppSetting表的ParamCode字段所有扩展属性
SELECT * FROM fn_listextendedproperty(default, 'user', 'dbo', 'table', 'AppSetting', 'column', 'ParamCode')
where objtype = 'COLUMN'
Code
if( exists(select 1 FROM fn_listextendedproperty('ControlType','user', 'dbo', 'table', 'AppSetting', 'column', 'ParamValue') where objtype = 'COLUMN') )
begin
exec sp_dropextendedproperty 'ControlType','user','dbo','table','AppSetting','column','ParamValue';
end;
exec sp_addextendedproperty 'ControlType', 'TextBox','user', 'dbo','table', 'AppSetting','column', 'ParamValue';
if( exists(select 1 FROM fn_listextendedproperty('ControlType','user', 'dbo', 'table', 'AppSetting', 'column', 'ParamValue') where objtype = 'COLUMN') )
begin
exec sp_dropextendedproperty 'ControlType','user','dbo','table','AppSetting','column','ParamValue';
end;
exec sp_addextendedproperty 'ControlType', 'TextBox','user', 'dbo','table', 'AppSetting','column', 'ParamValue';
Code
with cte
as
(
select 1 as ID , 0 Status
union all
select 2,1
union all
select 3,1
union all
select 3,0
union all
select 9,1
union all
select 0,1
)
select * from
(
--根据某项分组,取前几
select * ,rank() over(partition by Status order by ID) as GroupRank from cte
)t
where GroupRank <3
with cte
as
(
select 1 as ID , 0 Status
union all
select 2,1
union all
select 3,1
union all
select 3,0
union all
select 9,1
union all
select 0,1
)
select * from
(
--根据某项分组,取前几
select * ,rank() over(partition by Status order by ID) as GroupRank from cte
)t
where GroupRank <3
Code
--添加接口的检测类字段
alter table dbo.WebServiceInterface Add ProcessClass varchar(500) not null
--添加接口的检测类字段的唯一键,保证同一个接口只有一个检测类
ALTER TABLE WebServiceInterface ADD CONSTRAINT u_ProcessClass UNIQUE NONCLUSTERED(ProcessClass)
--删除列
alter table dbo.WebServiceInterface drop column ProcessClass
--删除已经存在的唯一键
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[WebServiceInterface]') AND name = N'u_ProcessClass')
ALTER TABLE [dbo].[WebServiceInterface] DROP CONSTRAINT [u_ProcessClass]
--添加接口的检测类字段
alter table dbo.WebServiceInterface Add ProcessClass varchar(500) not null
--添加接口的检测类字段的唯一键,保证同一个接口只有一个检测类
ALTER TABLE WebServiceInterface ADD CONSTRAINT u_ProcessClass UNIQUE NONCLUSTERED(ProcessClass)
--删除列
alter table dbo.WebServiceInterface drop column ProcessClass
--删除已经存在的唯一键
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[WebServiceInterface]') AND name = N'u_ProcessClass')
ALTER TABLE [dbo].[WebServiceInterface] DROP CONSTRAINT [u_ProcessClass]
Code
--将YYYYMMDD格式的字符串转成YYYY-MM-DD
declare @s varchar(50)
set @s = '20090102'
select substring(@s,1,4)+'-' + substring(@s,5,2) + '-' + substring(@s,7,2)
--将YYYYMMDD格式的字符串转成YYYY-MM-DD
declare @s varchar(50)
set @s = '20090102'
select substring(@s,1,4)+'-' + substring(@s,5,2) + '-' + substring(@s,7,2)