Useful T_ SQL statement favorites (continuous update)

 

1. Record group superposition

select c_GId,c_Id from t_Cargo 
where c_GId in ('MH-C2970','MX-B0794')

Query results:

select b.c_GId,LEFT(b.CargoList,len(b.CargoList)-1) from 
 (
select a.c_GId,
    (
select c_Id + ';' from t_Cargo
where c_GId=a.c_GId
for xml path('')
     ) as CargoList
from t_Cargo a
where a.c_GId in ('MH-C2970','MX-B0794')
group by a.c_GId
  )
b 

Query results:

-- Query the index SELECT d in each table. name table name, c. name index name, B. name index field, CASE c. type WHEN '1' then' aggregation 'when' 2' then' non-clustered 'end AS index type, c. is_unique is unique from sys. index_columns a left join sys. columns B ON (. column_id = B. column_id AND. object_id = B. object_id) left join sys. indexes c ON (. index_id = c. index_id AND. object_id = c. object_id) left join sys. tables d ON. object_id = d. object_id WHERE d. object_id = object_id ('guest ')

-- SELECT. NAME, B. definition, case when. xtype = 'v'then' view 'when. xtype = 'P' then' Stored Procedure 'else' other 'end AS type from sysobjects aLEFT JOIN sys. all_ SQL _modules bON. id = B. object_idWHERE. xtype IN ('V', 'P') and charindex ('not in', B. definition, 0)> 0 order by type DESC