本文汇总了SQL Server中实用的技巧，包括存储过程、触发器、视图的管理，字符串处理方法，动态SQL执行，分页查询实现，以及表和字段的详细信息导出等。
查看存储过程、触发器、视图 的内容
exec sp_helptext 'sp_Api_BackCharging'
--sp_Api_BackCharging是一个存储过程，引号这里写触发器、视图也同样能显示查看相关依赖关系
exec sp_depends 'Accounts_Users'
--Accounts_Users 是一张表，能看到这张表与其他相关的表、存储过程、视图……查看sysobjects 表中的内容
select * from sysobjects where xtype='TR'--触发器
select * from sysobjects where xtype='v'--视图
select * from sysobjects where xtype='p'--存储过程
select * from sysobjects where xtype='s'--系统表
select * from sysobjects where xtype='fn' --标量函数
select * from sysobjects where xtype='tf'--表函数相关博客：SQL Server系统表sysobjects介绍与使用 - 奔跑中的兔子 - 博客园
获取字符切割
declare @str nvarchar(30)
set @str='12|箱'
print substring (@str,1, charindex ('|',@str)-1) --输出数量12
print substring (@str, charindex ('|',@str)+1,len(@str)-charindex ('|',@str)) --输出箱
declare @ProductUnits nvarchar (20)
set @ProductUnits='1|盒,10|箱'
print substring (@ProductUnits,charindex(',',@ProductUnits)+1,len(@ProductUnits)-charindex(',',@ProductUnits))
--输出 10|箱执行字符串sql语句
exec('select * from TBCustomerOrder')
--冒号内容为要执行的sql使用遍历方法操作数据：
DECLARE @A1 VARCHAR ( 10 ),
@A2 VARCHAR ( 10 ),
@A3 INT DECLARE
YOUCURNAME CURSOR FOR SELECT
ProductId,
Id,
Qty
FROM
TBBrandLevelUpGiving OPEN YOUCURNAME FETCH NEXT
FROM
youcurname INTO @a1,@a2,@a3
while
@@fetch_status <>- 1 BEGIN--您要执行的操作写在这里
print @a1 FETCH NEXT
FROM
youcurname INTO @a1,@a2,@a3
END close youcurname deallocate youcurname临时表的是使用
DECLARE @tableTop TABLE ( Id int, Amount DECIMAL ( 12, 2 ) )
INSERT INTO @tableTop SELECT * FROM TBTopUpSet WHERE Amount > 1
SELECT
*
FROM
@tableTop动态分页查询
--01动态条件
ALTER PROCEDURE [dbo].[Test]
@flag int,
@inorout int
AS
BEGIN
SELECT * FROM Demo
where 1 = 1
and ((@flag IS NULL) or (flag=@flag) or @flag = '')
and ((@id IS NULL) or (id=@id) or @id= '')
END
--@flag是传入的参数
/*
so...如果要启用id作为查询条件，就传入一个非null的值，否则就是不启用id作为查询条件，这就是传说中的动态查询
*/
--02分页
select * from(
select *,rn= ROW_NUMBER() over(order by ApplyTime)
from TBCustomer
)as T2
where T2.rn
between (@Page-1)*@Rows+1
and @Rows*@Page
order by ID desc24小时内的时间
select Dateadd(HOUR,-24,GETDATE()) --返回当前时间减去24小时的时间随机获取表中一条数据
SET STATISTICS TIME ON
SET STATISTICS IO ON
SELECT TOP 1 vspCusid FROM TB_Api_VspCusid where State=2
GROUP BY vspCusid
ORDER BY NEWID()
SET STATISTICS TIME OFF
SET STATISTICS IO OFF根据逗号拆分，返回临时表
create FUNCTION [dbo].[func_split](@str nvarchar(4000),@separtor varchar(10))
returns @temp table([row] [int] IDENTITY(1,1) NOT NULL,valuess nvarchar(4000))
as
begin
declare @i int
set @str=rtrim(ltrim(@str))
set @i=charindex(@separtor,@str)
while @i>=1
begin
insert @temp values(left(@str,@i-1))
set @str=substring(@str,@i+1,len(@str)-@i)
set @i=charindex(@separtor,@str)
end
if @str<>''
insert @temp values(@str)
return
end
--使用方法：
select * from dbo.func_split('1,2,3,4,5,7', ',')动态条件查询_存在空值或者空字符串的情况处理
and (@State is null or A.State=@State)
and isnull(AuthCode,'') like (case when isnull(@AuthCode,'') = '' then isnull(AuthCode,'') else '%'+@AuthCode+'%' end)
and isnull(Name,'') = (case when isnull(@Name,'') = '' then isnull(Name,'') else @Name end)
and isnull(ApplyTime,'') >= (case when isnull(@BeginTime,'') = '' then isnull(ApplyTime,'') else convert(datetime,@BeginTime) end)
and isnull(ApplyTime,'') <= (case when isnull(@EndTime,'') = '' then isnull(ApplyTime,'') else convert(datetime,@EndTime) end)使用事务
BEGIN TRAN
pritn '在这里执行需要回滚的操作'
ROLLBACK TRAN
--------------------------------
BEGIN
set @Message=''
SET NOCOUNT ON;
BEGIN TRY---------------------开始捕捉异常
BEGIN TRAN------------------开始事务
UPDATE A SET A.names = B.names FROM T1 AS A INNER JOIN T2 AS B ON A.id = B.id
UPDATE A SET A.names = B.names FROM T1 AS A INNER JOIN T2 AS B ON A.TEST = B.TEST
COMMIT TRAN -------提交事务
END TRY-----------结束捕捉异常
BEGIN CATCH------------有异常被捕获
IF @@TRANCOUNT > 0---------------判断有没有事务
BEGIN
ROLLBACK TRAN----------回滚事务
END
set @Message='绑定出现问题'-----------记录存储过程执行时的错误信息，自定义
END CATCH--------结束异常处理
END获取随机数
DECLARE @NumBegin Int=1 --随机数的最小值
DECLARE @NumEnd Int=100 --随机数的最大值
DECLARE @Decimal Int=0 --保留小数点几位
SELECT @NumBegin+round((@NumEnd-@NumBegin)*rand(),@Decimal)整个库检索某个字段（如果存在返回表、对应的列）
declare @Str nvarchar(max),
@tableName varchar(50),
@colName varchar(50),
@rowCount int
select a.name tableName, b.name Colname, 0 as IsFound
into #t1
from sysobjects a
join syscolumns b on a.id=b.id
join systypes c on b.xtype=c.xtype
where a.[type]='U'
and c.name in ('varchar', 'nvarchar', 'char', 'nchar') --这里是设置字段的类型，以缩小范围
declare _c1 cursor for select Colname, tableName from #t1
open _c1
fetch next from _c1 into @colName, @tableName
while @@FETCH_STATUS=0
begin
--print @Str
select @Str='select @rowCount=count(1)
from ['+@tableName+'] where ['+@colName+'] like ''%售后反馈模板%''' --这里是要查找的内容
exec sp_executesql @Str, N'@rowCount int output', @rowCount output
if @rowCount>0 update #t1 set IsFound=1 where ColName=@colName and tableName=@tableName
fetch next from _c1 into @colName, @tableName
end
close _c1
deallocate _c1
select * from #t1 where IsFound=1
drop table #t1导出所有表和字段说明：
1\、执行下面的SQL,
2\、然后选择所有、
3\、另存为XXX
SELECT
表名 = Case When A.colorder=1 Then D.name Else '' End,
表说明 = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,
字段序号 = A.colorder,
字段名 = A.name,
字段说明 = isnull(G.[value],''),
标识 = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '√'Else '' End,
主键 = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '√' else '' end,
类型 = B.name,
占用字节数 = A.Length,
长度 = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
允许空 = Case When A.isnullable=1 Then '√'Else '' End,
默认值 = isnull(E.Text,'')
FROM
syscolumns A
Left Join
systypes B
On
A.xusertype=B.xusertype
Inner Join
sysobjects D
On
A.id=D.id and D.xtype='U' and D.name<>'dtproperties'
Left Join
syscomments E
on
A.cdefault=E.id
Left Join
sys.extended_properties G
on
A.id=G.major_id and A.colid=G.minor_id
Left Join
sys.extended_properties F
On
D.id=F.major_id and F.minor_id=0
--where d.name='OrderInfo' --如果只查询指定表,加上此条件
Order By
A.id,A.colorder根据日期统计订单数
select
SUBSTRING(CONVERT(varchar(100),WorkDate,120),1,4) 年,
SUBSTRING(CONVERT(varchar(100), WorkDate, 120),7,4) 日期,
count(ID) 订单量
from TBCustomerOrder
where
WorkDate>='2020-06-02 00:00:00'
and WorkDate<='2020-07-02 00:00:00'
group by SUBSTRING(CONVERT(varchar(100),WorkDate,120),1,4),
SUBSTRING(CONVERT(varchar(100), WorkDate, 120),7,4)
order by 年结果显示
判断表是否存在
if object_id ('TBCustomerOrderDetail') is not null
begin
print 'is not null'
endcast和convert的用法和区别
1、用法：
select CAST('123' as int) -- 123
select CONVERT(int, '123') -- 123
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
2、区别
CAST和CONVERT比较
(1)cast一般更容易使用,convert的优点是可以格式化日期和数值;
(2)convert一般用于日期和时间值，小数之间转换,cast一般用于小数转数值和字符型;
(3)converk显示转换,cast是强制转换; 递归
RETURN
(
WITH NODES
AS (
SELECT * FROM DBO.TBCustomerBrand par WITH (NOLOCK) WHERE par.Customer_ID=@Customer_ID and Brand_ID=@Brand_ID
UNION ALL
SELECT child.* FROM TBCustomerBrand AS child WITH (NOLOCK) INNER JOIN
NODES AS RC ON child.Brand_ParentId = RC.Customer_ID where child.Brand_ID=@Brand_ID)
--SELECT ID FROM TBCustomerBrand WHERE Customer_ID IN (SELECT Customer_ID FROM NODES N ) and Brand_ID=@Brand_ID
--SELECT ID FROM NODES N
SELECT ID FROM TBCustomerBrand WITH (NOLOCK) WHERE ID IN (SELECT ID FROM NODES N WITH (NOLOCK) )
)修改密码
update Accounts_Users set Password=
CONVERT(binary(42),0x7354069A8C451120D9B9922E43AEF46918D9F7B6) where UserID=15841创建聚合函数，拼接字符串
CREATE AGGREGATE [dbo].[hy_joinstr]
(@input [nvarchar](200))
RETURNS[nvarchar](max)
EXTERNAL NAME [MyAgg].[hy_joinstr]
GOwith as
SQL With As 用法 - Niko12230 - 博客园
获取生成的ID
@@identity ：返回当前会话最后一个标识值，不限于特定的作用域 ；
ident_current('tablename'):返回任何会话，任何作用域中的指定表中生成的
最后一个标识值 ；
scope_identity ：返回当前会话当前作用域任何表生成的最后一个标识值 。
时间转换成时分秒
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
扫一扫
970
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
