通过 SqlConnection 获取系统表和视图 [英] Getting system tables and views thru SqlConnection

问题描述

我正在尝试从 C# (VS 2012) 中的 LocalDB 数据库中获取所有表

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

当使用 OleDbConnection 时，我可以做到

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

如何使用 SqlConnection 执行此操作?似乎 SqlConnection 上的 GetSchema 只返回用户表和视图，但不返回系统表或视图.第 4 个限制参数似乎只接受 VIEW 和 BASE TABLE.

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); 

我可以使用该参数的另一个值来获取系统视图和表吗?还是有另一种方法可以使用 SqlConnection 检索系统视图和表?

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 ?

我无法使用 OleDbConnection，因为我必须使用 LocalDB，而且似乎无法使用 OleDbConnection 连接到 LocalDB.

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.

推荐答案

另一种方法是查询 SQL Server 目录视图.

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');

这篇关于通过 SqlConnection 获取系统表和视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！