Anatomy of SQL Server Nineth Orcamdf can now display metadata through System DMVs (translated)

Original: Anatomy of SQL Server Nineth Orcamdf can now display metadata through System DMVs (translated)

Anatomy of SQL Server Nineth Orcamdf can now display metadata through System DMVs (translated)

http://improve.dk/orcamdf-now-exposes-metadata-through-system-dmvs/

I sat on a train to Denmark and attended the last leg of the SQL Rally conference in northern Europe. In the course of the speech, I announced that orcamdf can read the metadata according to the work of Orcamdf.

Now, I might as well share it here. In addition to extending support for core engines in Orcamdf, one of the key features I want to implement is to display metadata about your database

How do your tables, columns, and databases unfold?

Avoid the abstraction of mistakes

My initial idea was to create my own abstraction layer underneath the upper object, which you can pass through the database. GetMetaData (). Usertables a list of all your users

You can get a list of user tables, including data columns. Consider this as a very clear interface from a development perspective, everything is normal. NET object.

However, this requires me to define the abstraction myself-how to divide what data needs to be exposed, what data cannot, how abstraction is most natural for DBAs, and need not be used

SQL Server-like sys. DMVs

Exposing the built-in DMVs from SQL Server

I spent some time thinking about what kind of people would end up using Orcamdf, and finally came to the conclusion that there could be only four people in the world who would use

The four categories are then split and eventually only DBAs and SQL Server developers. And they usually pass Dvms such as Sys.tables,sys.columns,sys.indexes

To browse the metadata for the SQL Server database. So that I can be confident that the power to develop this feature is that I have been able to parse out all the system tables and use SELECT object_definition ()

I was able to see the source code of the built-in system DMVs. So, creating a copy of my own built-in DMVs is a very simple thing.

How to use DMVs in Orcamdf

For example, we need to get information about all the columns of a table, and we will create the following SQL statement

SELECT     C. *  from     sys.columns c INNERJOIN    on C.object_id= T.object_idWHERE    ='Persons' 

The C # code in Orcamdf looks like this:

using(vardb =NewDatabase (New[] {@"C:test.mdf" })){    varSYS =db.    Dmvs; varTable = sys. Tables.where (t = T.name = ="Persons").    Single (); varcolumns = sys. Columns.where (c = C.objectid = =table.    ObjectID); foreach(varColinchcolumns) Console.WriteLine (Col. Name);}

If you prefer the syntax of Sql-esque LINQ, you can certainly do it as follows

using(vardb =NewDatabase (New[] {@"C:test.mdf" })){    varSYS =db.    Dmvs; varcolumns = fromCinchsys. Columns Join Tinchsys. Tables on C.objectid equals T.objectidwhereT.name = ="Persons"            SelectC; foreach(varColinchcolumns) Console.WriteLine (Col. Name);}

Whichever way you use it, the result will be as follows

What are the available DMVs

If you get to the latest submitted Orcamdf code, you can access the following DMVs, they are exposed through SQL Server

Sys.columnssys.indexessys.index_columnssys.objectssys.objects$sys.system_internals_allocation_unitssys.system_ Internals_partitionssys.system_internals_partition_columnssys.tablessys.types

If you have expectations of the DMV you can contact me, I will make your wish come true!

End of the Nineth chapter

Anatomy of SQL Server Nineth Orcamdf can now display metadata through System DMVs (translated)