Getting system tables and views thru SqlConnection

问题:

I am trying to get all tables from a LocalDB database in C# (VS 2012)

When using an OleDbConnection I can do

string[] restrictions = new string[4];
connection.GetSchema("Tables", restrictions); 

and it will return all user tables, all system views and all system tables

How can I do this with a SqlConnection ? It seems that GetSchema on a SqlConnection only returns user tables and views, but no system tables or views. The 4th restriction parameter only seems to accept VIEW and BASE TABLE.

string[] restrictions = new string[4];
restrictions[3] = 'BASE TABLE";
connection.GetSchema("Tables", restrictions); 

Is there another value I can use for this parameter to get the system views and tables ? Or is there another way I can retrieve system views and tables using a SqlConnection ?

I cannot use OleDbConnection because I have to use a LocalDB and there seems to be no way to connect to a LocalDB using OleDbConnection.

回答1:

An alternative method is a query of the SQL Server catalog views.

SELECT 
      OBJECT_SCHEMA_NAME(object_id) AS SchemaName
    , name AS ObjectName
    , type_desc AS ObjectType
FROM sys.system_objects
WHERE
    type_desc IN('USER_TABLE', 'SYSTEM_TABLE', 'VIEW')
UNION ALL
SELECT 
      OBJECT_SCHEMA_NAME(object_id) AS SchemaName
    , name AS ObjectName
    , type_desc AS ObjectType
FROM sys.objects
WHERE
    type_desc IN('USER_TABLE', 'SYSTEM_TABLE', 'VIEW');