SQL 获取数据库中的表和字段列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/420741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting list of tables, and fields in each, in a database
提问by GurdeepS
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?
我正在考虑创建一个基本的 ORM（纯粹是为了好玩），并且想知道有没有办法返回数据库中的表列表以及每个表的字段？
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).
使用这个，我希望能够遍历结果集（在 C# 中），然后对结果集中的每个表说，这样做（例如，使用反射制作一个将执行或包含 xyz 的类）。
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.
除此之外，还有哪些适用于 SQL Server 的优秀在线博客？我知道这个问题实际上是关于在 Sql Server 中使用系统 SP 和数据库，我对一般查询没问题，所以我对一些涵盖此类功能的博客感兴趣。
Thanks
谢谢
回答by MarlonRibunal
Is this what you are looking for:
这是你想要的：
Using OBJECT CATALOG VIEWS
使用对象目录视图
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';
Using INFORMATION SCHEMA VIEWS
使用信息架构视图
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;
Reference : My Blog - http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/
参考：我的博客 - http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/
回答by ZombieSheep
Tables ::
表 ::
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'
columns ::
列 ：：
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
or
或者
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='your_table_name'
回答by ShelS
Get list of all the tables and the fields in database:
获取数据库中所有表和字段的列表：
Select *
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_CATALOG Like 'DatabaseName'
Get list of all the fields in table:
获取表中所有字段的列表：
Select *
From INFORMATION_SCHEMA.COLUMNS
Where TABLE_CATALOG Like 'DatabaseName' And TABLE_NAME Like 'TableName'
回答by JeremyDWill
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
回答by Jie
I tested a few solutions an found that
我测试了一些解决方案，发现
Select *
From INFORMATION_SCHEMA.COLUMNS
gives you the column info for your CURRENT/default database.
为您提供当前/默认数据库的列信息。
Select *
From <DBNAME>.INFORMATION_SCHEMA.COLUMNS
, without the < and >, gives you the column info for the database DBNAME.
，没有 < 和 >，为您提供数据库 DBNAME 的列信息。
回答by ZombieSheep
Your other inbuilt friend here is the system sproc SP_HELP.
你的另一个内置朋友是系统 sproc SP_HELP。
sample usage ::
示例用法::
sp_help <MyTableName>
It returns a lot more info than you will really need, but at least 90% of your possible requirements will be catered for.
它返回的信息比您真正需要的要多得多，但至少可以满足您可能的 90% 的要求。
回答by Manni Marquesa
Just throwing this out there - easy to now copy/paste into a word or google doc:
只是把它扔在那里 - 现在很容易复制/粘贴到一个词或谷歌文档中：
PRINT '<html><body>'
SET NOCOUNT ON
DECLARE @tableName VARCHAR(30)
DECLARE tableCursor CURSOR LOCAL FAST_FORWARD FOR
SELECT T.name AS TableName
FROM sys.objects AS T
WHERE T.type_desc = 'USER_TABLE'
ORDER BY T.name
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tableName
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT '<h2>' + @tableName + '</h2>'
PRINT '<pre>'
SELECT LEFT(C.name, 30) AS ColumnName,
LEFT(ISC.DATA_TYPE, 10) AS DataType,
C.max_length AS Size,
CAST(P.precision AS VARCHAR(4)) + '/' + CAST(P.scale AS VARCHAR(4)) AS PrecScale,
CASE WHEN C.is_nullable = 1 THEN 'Null' ELSE 'No Null' END AS [Nullable],
LEFT(ISNULL(ISC.COLUMN_DEFAULT, ' '), 5) AS [Default],
CASE WHEN C.is_identity = 1 THEN 'Identity' ELSE '' END AS [Identity]
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
JOIN INFORMATION_SCHEMA.COLUMNS AS ISC ON T.name = ISC.TABLE_NAME AND C.name = ISC.COLUMN_NAME
WHERE T.type_desc = 'USER_TABLE'
AND T.name = @tableName
ORDER BY T.name, ISC.ORDINAL_POSITION
PRINT '</pre>'
FETCH NEXT FROM tableCursor INTO @tableName
END
CLOSE tableCursor
DEALLOCATE tableCursor
SET NOCOUNT OFF
PRINT '</body></html>'
回答by Jyotiranjan
For MYSQL:
对于 MYSQL：
Select *
From INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = "<DatabaseName>"
回答by brendan
This will get you all the user created tables:
这将为您提供所有用户创建的表：
select * from sysobjects where xtype='U'
To get the cols:
要获取 cols：
Select * from Information_Schema.Columns Where Table_Name = 'Insert Table Name Here'
Also, I find http://www.sqlservercentral.com/to be a pretty good db resource.
另外，我发现http://www.sqlservercentral.com/是一个非常好的数据库资源。
回答by Sampath Kumar S
I found an easy way to fetch the details of Tables and columns of a particular DB using SQL developer.
我找到了一种简单的方法来使用 SQL 开发人员获取特定数据库的表和列的详细信息。
Select *FROM USER_TAB_COLUMNS
