locked
How to get a sqlserver database metadata? RRS feed

  • Question

  • Hi Is there any way to get metadata of a sql server database in VSX application? For example list of tables and views and columns in a database. I can use SMO to get these information but its very slow in VSX application (The same works very quickly in a simple .Net application) is there any thing in VS itself. Any thing that I can use? thanks
    Sunday, January 2, 2011 2:30 PM

Answers

  • Hi

    I don't know about any special API. EF uses access to system tables but it is using its own ORM (I saw the SQL in profiler and it was certainly not work of some programmer). And it's slow like ____. Pure LINQ does not seem better.

    Do You have some particular VS wizard in mind? Maybe I can figure out how it's done.

    - Maybe You can first try to check, if there are any handled misterious exceptions while retrieving the schema.

    • Marked as answer by Victor_Chen Monday, January 10, 2011 7:59 AM
    Tuesday, January 4, 2011 5:46 PM

All replies

  • Hi Mehdi58,

    You can use direct access to system tables of MSSQL server like sys.tables, sys.columns, sys.indexes etc and read it in datasets or directly by readers. There are some views for that purpose too which may cover your needs. All located in schema INFORMATION_SCHEMA. For other DB servers a similar approach will work, although Yoy will need to implement it for every single SQL server.

    Other option is to use ADO.NET methods. They provides some common access to DB schema, but they are still provider specific. With some effort You can get it work with all basic DB servers like MSSQL, Oracle, DB2. I never tried it with others.

    This might help a little bit.

    Monday, January 3, 2011 8:21 AM
  • Hi
    I used to get schema information in the way you described but I wasn't happy with it. SMO is very cool, simple and reliable I want to know if VS has any thing for this purpose coz in some wizards in VS you can see it gets database schema and it gets it very fast. I use SMO in my package but it is very very slow (the same is very quick in a simple .net application) do you  know whats the reason or does VS have any API for this purpose that I can use?
    Monday, January 3, 2011 8:52 PM
  • Hi

    I don't know about any special API. EF uses access to system tables but it is using its own ORM (I saw the SQL in profiler and it was certainly not work of some programmer). And it's slow like ____. Pure LINQ does not seem better.

    Do You have some particular VS wizard in mind? Maybe I can figure out how it's done.

    - Maybe You can first try to check, if there are any handled misterious exceptions while retrieving the schema.

    • Marked as answer by Victor_Chen Monday, January 10, 2011 7:59 AM
    Tuesday, January 4, 2011 5:46 PM
  • Yes

    For example the SqlDataSource configuration wizard. it gets all tables and views and their columns very quickly.

    Thursday, January 6, 2011 9:36 PM
  • SqlDataSource designer is using DDEX - particularily: Microsoft.VisualStudio.Data.Services.IVsDataMappedObjectSelector

    I was digging in the code, but was unable to find out how to use it. I tried to look in MSDN, but I found nothing useful there.

    Monday, January 10, 2011 6:27 PM
  • Hi Fedi

    Something amazing happend I upgraded to SQL Server 2008 and it is quicker  now but it takes 5 to 10 secopns instead of more than 30 seconds.

    Friday, January 28, 2011 7:11 PM
  • For the record:

    It seems when I debug my application, SMO is 10 times slower. Please share if you know why.

    Saturday, April 21, 2012 9:42 AM