MSSQL - 零散知识积累

为表取别名

create synonym [同义表名] for dbo.[原表名]

FOR XML AUTO

• 直接转成xml输出

SELECT TOP 10
       A.iIden,
       A.sProductName,
       iProjectId = B.iIden,
       B.sProjectName
FROM dbo.dvProduct A
    JOIN dbo.dvProject B
        ON B.iProductId = A.iIden
FOR XML AUTO;

• 可将生成结果，直接写入xml变量

DECLARE @xmlDoc XML;
SET @xmlDoc =
(
    SELECT iIden, sGroup FROM dbo.pbIden WHERE iIden < 3 FOR XML RAW('aa')
);
SELECT @xmlDoc;

通过设置字符集区分大小写

• 区分大小写

ALTER TABLE 表名 ALTER COLUMN 列名 NVARCHAR(100) COLLATE Chinese_PRC_CS_AS; 

• 不区分大小写

ALTER TABLE 表名 ALTER COLUMN 列名 NVARCHAR(100) COLLATE Chinese_PRC_CI_AS;

创建临时表

• 1）动态SQL

DECLARE @sql VARCHAR(8000);
SET @sql = 'create table dbo.qmInspectClothesDtl (id bigint)';
EXEC (@sql);

• 2）定义表变量

DECLARE @t TABLE(id BIGINT)

• 3）临时表

SELECT CAST(NULL AS BIGINT) AS id
INTO #t
WHERE 1=1

分隔逗号

• 1）

declare @aa nvarchar(100) = '12,23,44'
select a.item from dbo.fnpbConvertStringToTableEx(@aa,',') a;

• 2）

SELECT value = SUBSTRING('1,2,33', b.number, CHARINDEX(',', '1,2,33' + ',', b.number) - b.number)
FROM master..spt_values b
 WHERE b.type = 'p'
           AND b.number BETWEEN 1 AND LEN('1,2,33')
AND  SUBSTRING(',' + '1,2,33', b.number, 1) = ',';

报错提示

RAISERROR('设备mac地址：%s在数据库中不存在！', 16, 1, @sDeviceMac);

创建事务（非即时提交型适用）

begin tran
[程序体]
[rollback/commit]

添加主键

alter table [表名] add constraint PK_[列名] Primary key ([列名])

添加唯一索引

alter table [dbo].hrDepartment add constraint ix_department_code unique(department_code);

查找主键

SELECT A.*
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A
WHERE A.TABLE_NAME='表名'

查找外键

• 1）

SELECT FK.NAME,FK.OBJECT_ID,OBJECT_NAME(FK.PARENT_OBJECT_ID) AS REFERENCETABLENAME
FROM SYS.FOREIGN_KEYS AS FK
JOIN SYS.OBJECTS AS O ON FK.REFERENCED_OBJECT_ID=O.OBJECT_ID
WHERE O.NAME = 'smLocalCompany'

• 2）

select name  
 from  sys.foreign_key_columns f join sys.objects o on f.constraint_object_id=o.object_id 
 where f.parent_object_id=object_id('表名') 

删除外键

ALTER TABLE dbo.smParameter DROP CONSTRAINT FK_smParameter_smLocalCompany_iCompanyId

开窗函数

SELECT sClassName=B1.sName,sStudentName=A1.sName,A1.iAge ,iRowNumber=Row_Number() 
OVER(PARTITION BY A1.iClassId ORDER BY A1.iAge DESC)
 FROM dbo.Student A1(NOLOCK) JOIN dbo.Class B1(NOLOCK) ON B1.iIden=A1.iClassId FROM ( ) A 
 WHERE A.iRowNumber=1

拼接

select *,stuff(select ',' + fieldname from table1 for xml path('')),1,1,'')  as  field from table2

修改列名

EXEC sp_rename ‘表名.[原有列名]’, ‘新列名' , 'COLUMN';

修改空列的列类型

alter table 表名 alter column 字段名 type not null

查询数据库中所有的存储过程，拼接成删除语句

SELECT 'drop proc '+[name]  FROM sys.all_objects
WHERE ([type] = 'P' OR [type] = 'X' OR [type] = 'PC')
AND [name] LIKE '%_InitMenu'
ORDER BY [name]; 

判断表中是否存在某列

IF NOT EXISTS (SELECT 1  FROM syscolumns INNER JOIN sysobjects ON sysobjects.id = syscolumns.id 
		WHERE syscolumns.name = 'sOrderNo' AND sysobjects.name = 'mmArrivalNoticeDtl')
ALTER TABLE mmArrivalNoticeDtl ADD sOrderNo NVARCHAR(50)
GO

设置字段允许为空

ALTER TABLE RFIDLocation ALTER COLUMN sRemark NVARCHAR(MAX) NULL;

判断唯一索引是否存在

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

判断表是否存在

SELECT COUNT(1) FROM information_schema.TABLES WHERE table_name ='ppWarning' AND TABLE_TYPE = 'BASE TABLE'

判断视图是否存在

IF OBJECTPROPERTY(OBJECT_ID(N'dbo.vw_2wms_invbill_clo'), N'IsView')=1
  print 1
GO

时间戳和日期相互转换（2017版本后都不支持了）

SELECT DATEADD(S,1160701488 + 8 * 3600,'1970-01-01 00:00:00')               --时间戳转换成普通时间
SELECT DATEDIFF(S,'1970-01-01 00:00:00', '2006-10-13 09:04:48.000') - 8 * 3600       --普通时间转换成时间戳

修改索引名

exec sp_rename N'表名.原索引名',N'新索引名',N'index';

设置主键

ALTER TABLE DBO.ppRollCall add primary key(id)

删除当前库所有的表数据

SELECT 
'BEGIN TRY TRUNCATE TABLE '+sOwner+'.'+sTableName+'; END TRY BEGIN CATCH END CATCH;'
+'BEGIN TRY DELETE '+sOwner+'.'+sTableName+' ; END TRY BEGIN CATCH END CATCH;'
FROM dbo.dvTables
WHERE bIsView = 0

查询包含某值的表名及列名

--创建过程
CREATE PROCEDURE [dbo].[SP_FindValueInDB]
(
    @value VARCHAR(1024)
)        
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @sql VARCHAR(1024) 
DECLARE @table VARCHAR(64) 
DECLARE @column VARCHAR(64) 
 
CREATE TABLE #t ( 
    tablename VARCHAR(64), 
    columnname VARCHAR(64) 
) 
 
DECLARE TABLES CURSOR 
FOR 
 
    SELECT o.name, c.name 
    FROM syscolumns c 
    INNER JOIN sysobjects o ON c.id = o.id 
    WHERE o.type = 'U' AND c.xtype IN (167, 175, 231, 239) 
    ORDER BY o.name, c.name 
 
OPEN TABLES 
 
FETCH NEXT FROM TABLES 
INTO @table, @column 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @sql = 'IF EXISTS(SELECT NULL FROM [' + @table + '] ' 
    SET @sql = @sql + 'WHERE RTRIM(LTRIM([' + @column + '])) LIKE ''%' + @value + '%'') ' 
    SET @sql = @sql + 'INSERT INTO #t VALUES (''' + @table + ''', ''' 
    SET @sql = @sql + @column + ''')' 
 
    EXEC(@sql) 
 
    FETCH NEXT FROM TABLES 
    INTO @table, @column 
END 
 
CLOSE TABLES 
DEALLOCATE TABLES 
 
SELECT * 
FROM #t 
 
DROP TABLE #t 
 
 
End

exec SP_FindValueInDB [要查的值]

查询存储过程内容

sp_helptext [过程名]

使用小技巧

按住alt，再用鼠标下拉几行的同一位置，可以实现多行录入，很实用

查询数据库内的存储过程内用到了某张特定的表

SELECT DISTINCT
	b.name,
	b.type
FROM	dbo.syscomments a,
		dbo.sysobjects b
WHERE a.id = b.id
AND b.xtype = 'p'
AND a.text LIKE '%表名%'
ORDER BY name

查询数据库中所有表的数据行数

select a.name as '表名',b.rows as '表数baidu据行数'
from sysobjects a inner join sysindexes b
on a.id = b.id
where a.type = 'u'
and b.indid in (0,1)
order by b.rows desc

用SQL语句创建相同结构的表

--Oracle的语句
create table sm_user0210 as select * from sm_user where 1=2;

--MSSQL的语句
select * into sm_user0210 from sm_user where 1=2;

查询数据库当前连接的服务器名称和实例名称

select '服务器名称' = @@servername,'实例名'=@@servicename

sqlserver统计一段时间内每半小时的订单总量

转载来源：https://blog.csdn.net/ljc0_01/article/details/103949349

select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,[订单创建时间]),120),dateadd(ss,-1,[订单创建时间]))/30)*30,convert(varchar(10),[订单创建时间],120)) as 时间段,  
count([订单号]) as 订单总量  
from  T_VALID_ORDER
where [订单创建时间]<'2017-12-31 23:59:59' and [订单创建时间]>'2016-01-01 00:00:00'
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,[订单创建时间]),120),dateadd(ss,-1,[订单创建时间]))/30)*30,convert(varchar(10),[订单创建时间],120))  
order by 时间段

为已有数据的表增加主键并赋值

ALTER TABLE dbo.[FileUploadExport$] ADD ID BIGINT identity(1,1)  PRIMARY KEY;

用身份证号计算年龄

SELECT A.sIDCardNo AS [身份证号],
       (CASE WHEN LEN(A.sIDCardNo) = 18 AND SUBSTRING(A.sIDCardNo,7,4) BETWEEN 1900 AND YEAR(GETDATE()) THEN 
FLOOR(DATEDIFF(DY, substring(A.sIDCardNo,7,4), GETDATE()) / 365.25) ELSE NULL END) AS [年龄]
FROM dbo.hrEmployee (NOLOCK) A

获取某月的所有日期

--Sql Server写法1
DECLARE @YearMonth VARCHAR(6);
SET @YearMonth = '202106';

DECLARE @date DATE;
SET @date = @YearMonth + '01';

DECLARE @i SMALLINT;
SET @i = 0;
WHILE @i < DATEDIFF(DAY, @date, DATEADD(MONTH, 1, @date))
BEGIN
    PRINT CONVERT(NVARCHAR(20), DATEADD(DAY, @i, @date), 120);
    SET @i = @i + 1;
END;

--Sql Server写法2
select convert(varchar(10),dateadd(dd,number,convert(varchar(8),getdate(),120)+'01'),120) as dt
from master..spt_values 
where type='P' 
and dateadd(dd,number,convert(varchar(8),getdate(),120)+'01')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,getdate()),120)+'01')

--Oracle写法
select to_date('2020-12','yyyy-mm')+rownum-1 from dual connect by rownum <=
(last_day(to_date('2012-12','yyyy-mm'))-to_date('2012-12','yyyy-mm')+1);

sql server 查询某个表的所有触发器名称

--查出所有用到某个表的SQL
select   *   from   sysobjects   where   xtype='TR'   
select   *   from   sysobjects   where   xtype='TR'   and   parent_obj=object_id('表名')

xtype char(2) 对象类型。可以是下列对象类型中的一种：
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图
X = 扩展存储过程

--查询有触发器的表
select name 表格名称 from sysobjects where xtype='U'  AND id in(select parent_obj from sysobjects where xtype='TR')

--查询没有触发器的表
select name 表格名称 from sysobjects where xtype='U'  AND id NOT in(select parent_obj from sysobjects where xtype='TR')

--有多少触发器用下面的就行：
select a.name 数据表名,sysobjects.name as 触发器名,sysobjects.crdate as 创建时间 from sysobjects 
left join (select *from sysobjects where xtype='U')as a on sysobjects.parent_obj=a.id
where sysobjects.xtype='TR'

本段转载来自：https://blog.csdn.net/qq_36260310/article/details/79699523，感谢原po

若某个存储过程被其他过程引用，查询引用的过程名

select distinct name
from syscomments a,sysobjects b   
where a.id=b.id    and b.xtype='p' and text like '%过程名%' 

判断值类型是否是数字

SELECT value,(CASE WHEN (isnumeric([value]) = 1) THEN CAST([value] AS bigint)  ELSE 0 END) AS value2 
FROM  [表名] A