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

Question

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

Answer 1

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'

Answer 2

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Answer 3

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.

Answer 4

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.

Answer 5

One advantage of the INFORMATION_SCHEMA approach is that it is quite portable across different databases.

Answer 6

Hi,

sqlhacks is quite useful. Most posts are quite simple (list all tables without PK, detach and delete the transaction log) however there are a few gems..

Answer 7

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 ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.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 ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS

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

Answer 8

Ideally I would like to say something like this:

SELECT * TABLES (and columns somehow) FROM DB_NAME

Without having to explicitly pass any names in other than the database name.

Any way of doing this? All the above examples require me to pass in the name of the table.