Getting list of tables, and fields in each, in a d

2019-01-03 23:41发布

站内问答 / 前端开发
831 12 5

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?

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

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.

Thanks

标签： sql tsql
举报
12条回答
▲ chillily
2楼-- · 2019-01-04 00:17 
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
查看更多
0人赞 添加讨论(0) 举报
冷血范
3楼-- · 2019-01-04 00:21

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>'
查看更多
0人赞 添加讨论(0) 举报
我只想做你的唯一
4楼-- · 2019-01-04 00:22

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.

查看更多
0人赞 添加讨论(0) 举报
不美不萌又怎样
5楼-- · 2019-01-04 00:22

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.

查看更多
0人赞 添加讨论(0) 举报
等我变得足够好
6楼-- · 2019-01-04 00:27

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
查看更多
0人赞 添加讨论(0) 举报
Melony?
7楼-- · 2019-01-04 00:28

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
查看更多
0人赞 添加讨论(0) 举报
上一页 1 2
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)