I'm very fortunate to be at SQL Server 2005 training in Melbourne all week this week. Our instructor is Aussie SQL Server MVP Rob Farley who blogs at msmvps.com/blogs/robfarley/ and is unbelievably knowledgeable about the product (of course).
So far I've collected 2 useful code snippets from Rob - more on useful stuff from the course later:
--show all plans in the cache, courtesy Rob Farley
--adding "OPTION (RECOMPILE)" means that subsequent executions
--of this query aren't added to the cache
SELECT *
FROM sys.dm_exec_cached_plans p CROSS APPLY
sys.dm_exec_sql_text(p.plan_handle) t CROSS APPLY
sys.dm_exec_query_plan(p.plan_handle) q
OPTION (RECOMPILE)
--comma-separated from resultset in 1 call, courtesy of Rob Farley
--here we'll return all database names in a comma-separated string
--the trick is to give no column name (in the inner select) and no
--argument to XML PATH
--STUFF replaces the first comma with an empty string
SELECT STUFF((
SELECT ',' + name
FROM sys.databases
ORDER BY name
FOR XML PATH('')
), 1, 1, '')
Enjoy!
Tags: sql server, database, training, snippet