SQL_数据库常用

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'