1、查询存储中是否含有某个字段

SELECT DISTINCT obj.Name 存储过程名
FROM syscomments sc 
INNER JOIN sysobjects obj ON sc.Id = obj.ID 
WHERE sc.TEXT LIKE '%' +N'usp_PAL_INFO_NEW' + '%' AND TYPE = 'P'  

--或

SELECT DISTINCT o.name, o.xtype 
FROM syscomments c 
INNER JOIN sysobjects o ON c.id=o.id 
WHERE c.TEXT LIKE '%tb_PAL_SKL_CORK_SNInfo%'

2、查询表的字段名、数据类型、长度

SELECT A.name as 表名,B.name as 列名,C.DATA_TYPE as 数据类型,B.max_length as 最大长度
FROM sys.tables A 
JOIN sys.columns B on B.object_id = A.object_id
JOIN INFORMATION_SCHEMA.COLUMNS C ON B.name=C.COLUMN_NAME and A.name=C.TABLE_NAME
WHERE a.name='shipment_header'

3、查询表的主键

SELECT TABLE_NAME 表名,COLUMN_NAME AS 主键名 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_NAME= 'shipment_header'

4、修改表结构

--修改字段名：
alter table 表名 rename column 旧名称 to 新名称
--修改字段类型：
alter table 表名 alter column 字段名 varchar(255) not null
--修改增加字段：
alter table 表名 ADD 字段名 类型 NOT NULL Default 0

5、MERGE INTO函数

含义：修改A表记录，匹配到A表的id与B表的AID相同的记录，就更新A表的year字段。没匹配到的就将B表记录插入到A表中。

MERGE INTO 	tb_SAPData_QSMCQBUS	AS  A
USING [tb_SAPData_BI_Interim]	AS B ON A.YEAR=B.YEAR AND A.GI_NO =B.GI_NO AND A.GI_ITEM=B.GI_ITEM  
WHEN NOT MATCHED  THEN
INSERT  (BU, PLANT, YEAR, GI_NO, GI_ITEM, DELIVERY, ITEM, QPN, TYPE, ZTYDL, MVT, QTY, MODEL, GIDATE, GITIME, VKORG, InsertTime)
VALUES(B.BU,B.PLANT, B.YEAR, B.GI_NO, B.GI_ITEM, B.DELIVERY, B.ITEM, B.QPN, B.TYPE,B.ZTYDL, B.MVT,B.QTY , B.MODEL, CONVERT(VARCHAR(20),CAST(B.GIDATE AS DATE),112),GITIME, B.VKORG,CONVERT(VARCHAR(50),GETDATE(),120));

6、执行带有output类型参数的存储过程

--执行带有output类型参数的存储过程
declare @result as varchar(5)
exec [usp_Fulfillment_Manual_SplitPallet_SAM] '','',@result output
select @result