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

SqlTsql

Sql Problem Overview

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 Solutions

Solution 1 - Sql

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/

Solution 2 - Sql

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'

Solution 3 - Sql

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'

Solution 4 - Sql

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Solution 5 - Sql

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.

Solution 6 - Sql

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.

Solution 7 - Sql

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

Solution 8 - Sql

SELECT * FROM INFORMATION_SCHEMA.COLUMNS for get all

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

Solution 9 - Sql

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.

Solution 10 - Sql

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

Solution 11 - Sql

For MYSQL:

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

Solution 12 - Sql

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

Solution 13 - Sql

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.

Categories
Recommended Sql Solutions
Recommended Tsql Solutions

Previous Solution:

Mirroring console output to a file

C#.NetFileTextConsole

Next Solution:

What is the difference between a C# Reference and a Pointer?

C#PointersReference

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionGurdeepSView Question on Stackoverflow
Solution 1 - SqlMarlonRibunalView Answer on Stackoverflow
Solution 2 - SqlZombieSheepView Answer on Stackoverflow
Solution 3 - SqlShelSView Answer on Stackoverflow
Solution 4 - SqlJeremyDWillView Answer on Stackoverflow
Solution 5 - SqlJieView Answer on Stackoverflow
Solution 6 - SqlZombieSheepView Answer on Stackoverflow
Solution 7 - SqlManni MarquesaView Answer on Stackoverflow
Solution 8 - Sqlnafaa jamelView Answer on Stackoverflow
Solution 9 - SqlbrendanView Answer on Stackoverflow
Solution 10 - SqlTony GalloneView Answer on Stackoverflow
Solution 11 - SqlJyotiranjanView Answer on Stackoverflow
Solution 12 - SqlSampath Kumar SView Answer on Stackoverflow
Solution 13 - SqlcjonasView Answer on Stackoverflow