improve.dk - dynamically recreating common DMVs

OrcaMDF Now Exposes Metadata Through System DMVs | Mark S. Rasmussen

Improve.dk Spined HTML

OrcaMDF Now Exposes Metadata Through System DMVs | Mark S. Rasmussen Mark S. Rasmussen improve.dkWell-nighMe Pages Categories Archive Nov 10 2011 OrcaMDF Now Exposes Metadata Through System DMVs SQL Server - OrcaMDF Comments I’m sitting here on the train in Denmark, on the final leg home from SQLRally Nordic. During my presentation based on my OrcaMDF work, I implicitly spoken that OrcaMDF now exposes metadata – thougt I might as well share here as well. Other than expanding the cadre engine support in OrcaMDF, one of the main features I’ve wanted to implement was a way for OrcaMDF to expose metadata well-nigh your database. How do you list the tables, indexes, columns, etc. from your database? Avoiding false abstractions My initial thought was to create my own wresting layer on top of the objects. You could get the list of user tables by accessing the database.GetMetadata().UserTables enumeration, you’d get a list of tables, including columns, etc. This has a very wipe interface from the minutiae side, everything stuff normal .NET objects. However, it would moreover require me to come up with said wresting – and where do I yank the line on what to expose and what to alimony internal? What if my wresting didn’t finger natural to DBAs, stuff used to the sys.* DMVs from SQL Server? Exposing the seated DMVs from SQL Server I spent some time considering who might end up using OrcaMDF – and terminated there might be just well-nigh four persons in the world, and those four would be split evenly between DBA and SQL Server dev. Common for those is that they’re once used to navigating the metadata of SQL Server databses through system DMVs like sys.tables, sys.columns, sys.indexes etc. What then struck me was that I’m once worldly-wise to parse all of the wiring tables in SQL Server, and using OBJECT_DEFINITION, I can get the source lawmaking of the seated system DMVs. As such, it was a simple matter of creating my own replicas of the seated DMVs. How to use the DMVs in OrcaMDF Say we wanted to retrieve all the columns for a given data in SQL Server, we create a query like this: SELECT c.* FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = 'Persons' Doing the same in OrcaMDF could squint like this: using (var db = new Database(new[] { @"C:Test.mdf" })) { var sys = db.Dmvs; var table = sys.Tables.Where(t => t.Name == "Persons").Single(); var columns = sys.Columns.Where(c => c.ObjectID == table.ObjectID); foreach (var col in columns) Console.WriteLine(col.Name); } And if you prefer the increasingly SQL-esque syntax of LINQ, you can of undertow do it like this as well: using (var db = new Database(new[] { @"C:Test.mdf" })) { var sys = db.Dmvs; var columns = from c in sys.Columns join t in sys.Tables on c.ObjectID equals t.ObjectID where t.Name == "Persons" select c; foreach (var col in columns) Console.WriteLine(col.Name); } No matter how you segregate to do it, this is the result: What’s misogynist at this point If you grab the latest commit of OrcaMDF, you’ll have wangle to the pursuit DMVs, just as they’re exposed through SQL Server: sys.columns sys.indexes sys.index_columns sys.objects sys.objects$ sys.system_internals_allocation_units sys.system_internals_partitions sys.system_internals_partition_columns sys.tables sys.typesIncreasinglyis definitely on their way. Let me know if you have a special wish for a DMV – I might just be worldly-wise to make your wish come true! Mark S. Rasmussen I'm the CTO at iPaper where I petting with databases, mold lawmaking and maintain the overall technical & team responsibility. I'm an voracious speaker at user groups & conferences. I love life, motorcycles, photography and all things technical. Say hi on Twitter, write me an email or squint me up on LinkedIn. CATEGORIES .NETAS/Flex/FlashAmazon Web ServicesComputer ScienceConferences and PresentingIISLifeMiscMiscellaneousPerformancePokerSQL ServerSQL Server - CommunitySQL Server - Data TypesSQL Server - InternalsSQL Server - OptimizationSQL Server - OrcaMDFSQL Server - TricksTestingTools of the TradeUmbracoVisual StudioWebWindbgWindows ARCHIVE 201420132012201120102009200820072006 Copyright © 2014 Mark S. Rasmussen