t-sql 笔记

1.查询哪些数据库对象使用了某个表

SELECT b.[name], a.[definition]
FROM sys.all_sql_modules a, sysobjects b
WHERE a.[object_id] = id AND definition LIKE ‘%表名%‘

2.查询表的某一列，将结果变成用逗号分隔的字符串

select col+‘,‘ from mytable for xml path(‘‘)

3.查询有哪些表的表名包含“storeroom”

 select * from sysobjects obj where LOWER(obj.name) LIKE N‘%storeroom%‘ and xtype=‘U‘

4.分组条件求和

DECLARE @t1 TABLE ( c1   NUMERIC (12), c2   VARCHAR (30) )
INSERT INTO @t1 (c1, c2) VALUES (1, ‘a‘);
INSERT INTO @t1 (c1, c2) VALUES (2, ‘a‘);
INSERT INTO @t1 (c1, c2) VALUES (3, ‘b‘);
INSERT INTO @t1 (c1, c2) VALUES (4, ‘b‘);
SELECT CASE WHEN max (c1) > 3 THEN sum (c1) ELSE 0 END AS c
  FROM @t1
GROUP BY c2;
/*
结果:
c
0
7
*/

x.待续