locked
How to get list of tables and list of columns in those tables in EF RRS feed

  • Question

  • Hi All,

    I have an application using Entity framework. Is there any method to get the list of all tables in the current database. I also need to get the list of columns in each table.

    Wednesday, November 8, 2017 11:13 AM

All replies

  • No, not that I know about. You can use T-SQL scripts that you can run with a stored procedure that you could get the results for about what you are looking for that can be run using the EF backdoor.
    Wednesday, November 8, 2017 4:55 PM
  • I am guessing but it is possible to create an EF model from a View or Stored Procedure, right? That might help.


    Sam Hobbs
    SimpleSamples.Info

    Wednesday, November 8, 2017 8:04 PM
  • Hi SQLNeophyte,

    If you want to get all the tables name and related columns name from the current database, As DA924x said, you could use raw SQL to achieve it. like this:

     using (var db = new EFDemoContext())
                {
                      var query = db.Database.SqlQuery<string>("SELECT t.name FROM sys.tables t").ToList();
    
                    foreach (var item in query)
                    {
                        Console.WriteLine("table name:{0}", item);
                        var subquery = db.Database.SqlQuery<string>(string.Format(@"SELECT c.name  
                            FROM  sys.columns c
                            JOIN  sys.tables  t   ON c.object_id = t.object_id
                            WHERE  t.name = '{0}'",item));
    
                        foreach (var subitem in subquery)
                        {
                            Console.WriteLine("column name:{0}",subitem);
                        }
                    }
    
                    Console.ReadKey();

    If you want to retrieve related tables name in entity framework models, you could not try the following sample code.

    #Query related tables information.

    using (var db = new EFDemoContext())
                {
                    var metadata = ((IObjectContextAdapter)db).ObjectContext.MetadataWorkspace;
                    var tables = metadata.GetItemCollection(DataSpace.SSpace)
                                .GetItems<EntityContainer>()
                                .Single()
                                .BaseEntitySets
                                .OfType<EntitySet>()
                                .Where(s => !s.MetadataProperties.Contains("Type")
                                || s.MetadataProperties["Type"].ToString() == "Tables");
    
                    foreach (var table in tables)
                    {
                        var tableName = table.MetadataProperties.Contains("Table")
                            && table.MetadataProperties["Table"].Value != null
                            ? table.MetadataProperties["Table"].Value.ToString()
                            : table.Name;
    
                        var tableSchema = table.MetadataProperties["Schema"].Value.ToString();
    
                        Console.WriteLine(tableSchema + "." + tableName);
                    }
    }

    About columns information, please refer to the following link.

    https://romiller.com/2015/08/05/ef6-1-get-mapping-between-properties-and-columns/

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, November 9, 2017 6:42 AM