数据库常用查询sql语句

1.查询用户自建的表约束

SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint, --约束名
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName, --表名
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc LIKE '%CONSTRAINT'
and OBJECT_NAME(OBJECT_ID) like 'SYS_ACTION_OPERATE_LOG%' --约束名

2. 查询MOM_PRD_ZENNER 库中有多少张表

select count(1) from sysobjects where xtype='U'

3. 查询每个分组中最大的一条记录

--查询每个分组中最大的一条记录: 按照PRODUCTION_NO 分组,按照BOMVersion 排序
select is_delete,status,BOMVersion,PRODUCTION_NO,Production_name,group_name, * from (
SELECT  ROW_NUMBER()   
        over   
        (PARTITION By PRODUCTION_NO order by BOMVersion) as rowId,pm_bom_group.*
FROM pm_bom_group 
) t
where rowid <= 1
and PRODUCTION_NO IN('HF3005011216','HF3005007010','HF3801011206','HF3005001010')