T-SQL便笺

动态执行SQL：

DECLARE @SQL NVARCHAR(max)

……

EXEC SP_EXECUTESQL @SQL  

动态执行SQL带参数：

DECLARE @sys_sp_name nvarchar(100),@sys_sql nvarchar(max)

select @sys_sp_name='存储过程名称' 

  if EXISTS (SELECT name FROM sysobjects WHERE name =@sys_sp_name  AND type = 'P')

    begin

        select @sys_sql=N'exec '+@sys_sp_name+' @PageSize,@Page,@strTJ,@RowCount output'

        EXEC sp_executesql @sys_sql,

            N'@PageSize bigint,@Page bigint,@strTJ nvarchar(2000),@RowCount bigint out',

            @PageSize,@Page,@strTJ,@RowCount output

        RETURN

    end

查询存储过程：

SELECT name FROM sysobjects WHERE name = '……' AND type = 'P'

查询函数：

select * from dbo.sysobjects where id = object_id(N'[dbo].[……]') and xtype in (N'FN', N'IF', N'TF')

查询表值类型:

select * from dbo.sysobjects where xtype='TT' and substring(name,4,len(……))=……

查询索引：

SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID('stb632', N'U') and NAME='index_Attendance_Time'

查询列名：顺序column_id 列名name

临时表：

SELECT name FROM tempdb.sys.columns

WHERE object_id =object_id('tempdb..#ReportData')

普通表:

SELECT name

FROM sys.all_columns

WHERE  object_id=OBJECT_ID(N'stb801',N'U') AND system_type_id=108 AND name NOT IN('病假','事假','产假')

 

 

区域

NodeName   NVARCHAR(200) COLLATE Database_Default

SELECT ' '+Substring(CONVERT(NVARCHAR(20),SignTime,120),12,10)

FROM   #AttendMachineRecord

WHERE  #AttendMachineRecord.NodeID=#PeopleDailyList.NodeID

AND 日期=CAST(SignTime AS DATE)

AND SN=考勤机  collate Chinese_PRC_CI_AS

ORDER BY SignTime ASC

排名:

SELECT NodeID,姓名,工号,部门,考核项目,综合平均得分,RANK() over(order by 综合平均得分 DESC) as 得分排名

FROM  #midtt

分组排名:

SELECT row_number()over(PARTITION BY 考勤人员类别 ORDER BY 开始时间),考勤人员类别,开始时间,结束时间

FROM #TimeRuleConfig

WHERE 是否判定异常=0

 

按姓名笔画排序:

SELECT * FROM TableName ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_ci_as

Windows collation suffix	Sort order description
_BIN1	Binary sort
_BIN21, 2	Binary-code point sort order
_CI_AI2	Case-insensitive, accent-insensitive, kana-insensitive, width-insensitive
_CI_AI_KS2	Case-insensitive, accent-insensitive, kana-sensitive, width-insensitive
_CI_AI_KS_WS2	Case-insensitive, accent-insensitive, kana-sensitive, width-sensitive
_CI_AI_WS2	Case-insensitive, accent-insensitive, kana-insensitive, width-sensitive
_CI_AS2	Case-insensitive, accent-sensitive, kana-insensitive, width-insensitive
_CI_AS_KS2	Case-insensitive, accent-sensitive, kana-sensitive, width-insensitive
_CI_AS_KS_WS2	Case-insensitive, accent-sensitive, kana-sensitive, width-sensitive
_CI_AS_WS2	Case-insensitive, accent-sensitive, kana-insensitive, width-sensitive
_CS_AI2	Case-sensitive, accent-insensitive, kana-insensitive, width-insensitive
_CS_AI_KS2	Case-sensitive, accent-insensitive, kana-sensitive, width-insensitive
_CS_AI_KS_WS2	Case-sensitive, accent-insensitive, kana-sensitive, width-sensitive
_CS_AI_WS2	Case-sensitive, accent-insensitive, kana-insensitive, width-sensitive
_CS_AS2	Case-sensitive, accent-sensitive, kana-insensitive, width-insensitive
_CS_AS_KS2	Case-sensitive, accent-sensitive, kana-sensitive, width-insensitive
_CS_AS_KS_WS2	Case-sensitive, accent-sensitive, kana-sensitive, width-sensitive
_CS_AS_WS2	Case-sensitive, accent-sensitive, kana-insensitive, width-sensitive

 

Collation and Unicode support - SQL Server | Microsoft Docs