Tuesday's Melbourne SQL User Group meeting was packed full of people to hear Kalen Delaney speak about metadata in SQL Server 2000 and the upcoming SQL Server 2005 (the release formerly known as "Yukon"). I hadn't been to a monthly group for 3 months so it was good to get back into it and I admit I was expecting big things from Kalen, expecially knowing she's the author of "Inside SQL Server 2000", which is still recommended as one of the better books on SQL Server 2000 (even 5 years after the book was released).
Kalen didn't disappoint with her knowledge and ability to explain the inner working of SQL Server. There was a hitch getting the latest beta of SQL Server 2005 to work though, so we had to be satisfied with theory rather than practice when it came to the "new bits". I still found Kalen's thorough coverage of all the ways you could get at metadata - "data about data" - really interesting and I learnt a few things too (during the session I had a strange thought - since SQL Server stores its metadata in tables, which are themselves described by metadata, where does the recursive nightmare stop?)
I use metadata (unwittingly) in different ways - recently I played around with allowing a user to script a whole database and had to mess with several metadata-type functions e.g. my attempt to document sp_msobjectprivs. I probably utilise metadata more than I know, for instance when Visual Studio discovers the return structure from a stored procedure when creating a Reporting Services report or a strongly-typed DataSet.
Kalen went on to discuss accessing metadata through system stored procedures, system functions, property functions, information schema views and system tables (thankfully, she said that SQL Server 2005 combines most of these methods into a common, understandable "sys" schema and catalog views where there is very little duplication). Another advantage of the upcoming SQL Server release was row-level security on metadata access: users will only be able to see what they've been given access to. She had an interesting demonstration that showed some "system tables" in SQL Server 2000 aren't really tables, and we can find this out by checking the used space:
select * from sysfiles
exec sp_spaceused 'sysfiles'
In summary then, Tuesday's meeting was great. It's good that we can get such a high-quality speaker as Kalen, who was easy to understand even though her "master" database, which I think of more like "marstar", became "masster", and her "data" came out like "day-tah" ;-)
Looking forward to next month...