Wednesday, March 26, 2008

Global functions in SQL 2005/2008

Looking at a new version of software always seems to uncover features that would have been useful in the past.  How about this one?  A stored proc that is globally available across databases?  It's also available in SQL 2005.  Here's one of the engine mechanics of SQL hacking around.

So - I present to you sp_AllocationMetadata. I was having all kinds of trouble using it in other databases (trying to figure out a way to change database contexts in the SP) until I remembered that you can create an SP in master and mark it as a system object using the undocumented sys.sp_MS_marksystemobject SP. This makes any SP execute in the context of the database from where it is called - extremely useful when you're querying against a database's system catalog views

In Recovery... - Paul S. Randal on SQL Server

Next up I'm going to try and find time to look at FileStream data type and see whether it's useful to catalog the contents of a hard drive or something...

No comments: