获取数据库中的表和每个表中的字段的列表

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

使用这个函数，我希望能够循环遍历结果集(在 C # 中) ，然后对结果集中的每个表这样做(例如，使用反射创建一个将执行 xyz 或包含 xyz 的类)。

除此之外，SQLServer 还有哪些好的在线博客？我知道这个问题实际上是关于在 Sql Server 中使用系统 SP 和数据库的，我对一般查询没有问题，所以我对一些涵盖此类功能的博客感兴趣。

谢谢

292656 次浏览
小开

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'
小开 
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
小开

Your other inbuilt friend here is the system 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.

小开

This will get you all the user created tables:

select * from sysobjects where xtype='U'

To get the 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.

小开
最佳答案

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 ,
C.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/

小开

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'
小开

This will return the database name, table name, column name and the datatype of the column specified by a database parameter:

declare @database nvarchar(25)
set @database = ''


SELECT cu.table_catalog,cu.VIEW_SCHEMA, cu.VIEW_NAME, cu.TABLE_NAME,
cu.COLUMN_NAME,c.DATA_TYPE,c.character_maximum_length
from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE as cu
JOIN INFORMATION_SCHEMA.COLUMNS as c
on cu.TABLE_SCHEMA = c.TABLE_SCHEMA and c.TABLE_CATALOG =
cu.TABLE_CATALOG
and c.TABLE_NAME = cu.TABLE_NAME
and c.COLUMN_NAME = cu.COLUMN_NAME
where cu.TABLE_CATALOG = @database
order by cu.view_name,c.COLUMN_NAME
小开

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.

小开

I found an easy way to fetch the details of Tables and columns of a particular DB using SQL developer.

Select *FROM USER_TAB_COLUMNS
小开

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>'
小开

For MYSQL:

Select *
From INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = "<DatabaseName>"
小开

SELECT * FROM INFORMATION_SCHEMA.COLUMNS for get all

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS for get all table name. Try it on sqlserver,

小开

in a Microsoft SQL Server you can use this:

declare @sql2 nvarchar(2000)
set @sql2  ='
use ?
if (  db_name(db_id()) not in (''master'',''tempdb'',''model'',''msdb'',''SSISDB'')  )
begin


select
db_name() as db,
SS.name as schemaname,
SO.name tablename,
SC.name columnname,
ST.name type,
case when ST.name in (''nvarchar'', ''nchar'')
then convert(varchar(10), ( SC.max_length / 2 ))
when ST.name in (''char'', ''varchar'')
then convert(varchar(10), SC.max_length)
else null
end as length,
case when SC.is_nullable = 0 then ''No'' when SC.is_nullable = 1 then ''Yes'' else null end as nullable,
isnull(SC.column_id,0) as col_number
from sys.objects                  SO
join sys.schemas                  SS
on SS.schema_id = SO.schema_id
join sys.columns             SC
on SO.object_id     = SC.object_id
left join sys.types               ST
on SC.user_type_id = ST.user_type_id and SC.system_type_id = ST.system_type_id
where SO.is_ms_shipped = 0
end
'


exec sp_msforeachdb @command1 = @sql2

this shows you all tables and columns ( and their definition ) from all userdefined databases.


提交答案