In LINQPad can you access SYSOBJECTS using LINQ?

Question

In LINQPad is there any way to access either the SYSOBJECTS table or the various INFORMATION_SCHEMA.xxx views using LINQ?

I spend a lot of time searching through our huge company database for partial names as there are too many tables and Stored Procedures to remember the names of them all.

I know I can enter and run SQL in LINQPad but I would like to do this in LINQ instead of SQL as LINQ is more fun :)

Thanks

Xanthalas

Answer 1

Yes you can.

All you have to do is include system views and SPs in selected connection and use LINQ like following:

sys.Sysobjects.Where(sp => sp.Xtype == "P")  // returns SPs
sys.Sysobjects.Where(t => t.Xtype == "U")    // returns Tables

or using sys.Views directly [example returns all tables with columns containing string "person" ]:

sys.Tables.Join(sys.Columns,
                t => t.Object_id,
                c => c.Object_id,
                (t, c) => new { t, c })
    .Where(x => x.c.Name.Contains("person"))
    .Select(x => new { ObjName = x.t.Name,
                       ChildName = x.c.Name } )
            .Distinct()