获取数据库中的表和字段列表 [英] Getting list of tables, and fields in each, in a database

问题描述

我正在考虑创建一个基本的 ORM(纯粹是为了好玩)，并且想知道有没有办法返回数据库中的表列表以及每个表的字段?

I'm looking at creating a basic ORM (purely for fun), and was wondering, is there a way to return the list of tables in a database and also the fields for every table?

使用这个，我希望能够遍历结果集(在 C# 中)，然后对结果集中的每个表说，这样做(例如，使用反射制作一个将执行或包含 xyz 的类).

Using this, I want to be able to loop through the result set (in C#) and then say for each table in the result set, do this (e.g. use reflection to make a class that will do or contain xyz).

除此之外，还有哪些适用于 SQL Server 的优秀在线博客?我知道这个问题实际上是关于在 Sql Server 中使用系统 SP 和数据库，我可以接受一般查询，所以我对一些涵盖此类功能的博客感兴趣.

Further to this, what are some good online blogs for SQL Server? I know this question is really about using system SPs and databases in Sql Server, and I am ok with general queries, so I'm interested in some blogs which cover this sort of functionality.

谢谢

推荐答案

这是您要找的:

使用对象目录视图

 SELECT T.name AS Table_Name ,
       C.name AS Column_Name ,
       P.name AS Data_Type ,
       P.max_length AS Size ,
       CAST(P.precision AS VARCHAR) + '/' + CAST(P.scale AS VARCHAR) AS Precision_Scale
FROM   sys.objects AS T
       JOIN sys.columns AS C ON T.object_id = C.object_id
       JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE  T.type_desc = 'USER_TABLE';

使用信息架构视图

  SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
       COLUMN_DEFAULT ,
       DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH ,
       NUMERIC_PRECISION ,
       NUMERIC_PRECISION_RADIX ,
       NUMERIC_SCALE ,
       DATETIME_PRECISION
FROM   INFORMATION_SCHEMA.COLUMNS;

参考:我的博客 - http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/

Reference : My Blog - http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/

这篇关于获取数据库中的表和字段列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！