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

Re; SQL Server Blog List - take a look at this entry in my blog: http://dbalink.wordpress.com/2009/01/07/todays-link-sql-server-blog-list/ — MarlonRibunal, Jan 07 '09 at 17:46
See related dup question: http://stackoverflow.com/questions/175415/how-do-i-get-list-of-all-tables-in-a-database-using-tsql — Ray, Feb 14 '09 at 00:07

score 191 · Accepted Answer · edited Oct 26 '22 at 23:30

191

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/

edited Oct 26 '22 at 23:30

Ender

14,995
8
36
51

answered Jan 07 '09 at 17:31

MarlonRibunal

4,009
3
31
37

9

The first query always returns maxlength=8000 for char, which is incorrect. The second query is correct and more detailed – smirkingman Mar 19 '14 at 09:15
1

both queries returning different number of rows :/ – GorvGoyl Apr 04 '17 at 13:40
One advantage of the `INFORMATION_SCHEMA` approach is that it is quite portable across different databases. – j_random_hacker Jan 03 '18 at 10:35
1

Is that a Typo? I'd use `C.max_length AS Size ,` - otherwise you'd end up with maxlength=8000 just as @smirkingman mentioned. – mbx Jan 24 '18 at 07:57
I find the 1st query doesn't list schemas and also returns too many rows (duplicates) and the 2nd query is returning the correct number of rows. So 2nd query is the one to use. – Gary Barrett Sep 07 '18 at 11:28
10 year old question and still valuable. Thanks for the question and the answer! – Mike Marshall Jan 15 '19 at 15:06
Per comments from @smirkingman and mbx I have edited the answer to be more correct. – Ender Oct 26 '22 at 23:32
I fixed the issue with the first query in this answer. See my answer below: https://stackoverflow.com/a/76640299/47191 – Jon Jul 07 '23 at 21:22

score 43 · Answer 2 · answered Jan 07 '09 at 15:36

43

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'

answered Jan 07 '09 at 15:36

ZombieSheep

29,603
12
67
114

8

The cool thing about INFORMATION_SCHEMA is that it's an ISO thing, not just a sql server thing. The same code will work for all compliant databases – cindi Jan 07 '09 at 19:07
That's very useful...but how to include database name in above expression? Thanks in advance.. – Amit Verma Oct 03 '16 at 10:44
@AmitVerma `USE ; SELECT * FROM INFORMATION_SCHEMA.COLUMNS` (MS SQL syntax at least) – Chris O Jan 21 '19 at 22:32

score 15 · Answer 3 · edited Oct 31 '12 at 22:00

15

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'

edited Oct 31 '12 at 22:00

user427969

3,836
6
50
75

answered Oct 31 '12 at 21:33

ShelS

177
1
2

One advantage of the `INFORMATION_SCHEMA` approach is that it is quite portable across different databases. – j_random_hacker Jan 03 '18 at 10:35

score 9 · Answer 4 · edited Oct 24 '16 at 18:15

9

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.

edited Oct 24 '16 at 18:15

Petter Friberg

21,252
9
60
109

answered Oct 24 '16 at 17:57

Jie

1,107
1
14
18

score 8 · Answer 5 · answered Jan 07 '09 at 15:36

8

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

answered Jan 07 '09 at 15:36

JeremyDWill

3,132
21
18

score 3 · Answer 6 · answered Jan 07 '09 at 15:41

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.

answered Jan 07 '09 at 15:41

ZombieSheep

29,603
12
67
114

score 1 · Answer 7 · answered Sep 11 '17 at 22:06

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

score 1 · Answer 8 · edited Mar 01 '18 at 22:26

1

SELECT * FROM INFORMATION_SCHEMA.COLUMNS for get all

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

edited Mar 01 '18 at 22:26

Goose

4,764
5
45
84

answered Mar 01 '18 at 22:12

nafaa jamel

11
1

score 0 · Answer 9 · answered Feb 10 '16 at 16:59

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

For all *Views*, this lists the table name, column name, data type, and length of every column returned by the view. This doesn't answer the question but it is a neat query. — Ben, Nov 05 '20 at 16:33

score 0 · Answer 10 · answered Jan 07 '09 at 15:42

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.

Jyotiranjan · Answer 11 · 2019-02-15T09:58:28.983

0

For MYSQL:

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

edited Feb 15 '19 at 09:58

answered Jan 03 '18 at 06:24

Jyotiranjan

683
8
20

`TABLE_SCHEMA` isn't the database name, its the Schema name (i.e. `dbo`). `TABLE_CATALOG` is the database name. – DDuffy Feb 14 '19 at 14:30
TABLE_SCHEMA: The name of the schema (database) to which the table belongs. TABLE_CATALOG: The name of the catalog to which the table belongs. – Jyotiranjan Feb 15 '19 at 07:03
in mysql. not mssql. Dont get me wrong, im not saying your answer was incorrect. It is correct, just not for mssql. I believe they treat schema's differently in both. – DDuffy Feb 15 '19 at 09:45

score 0 · Answer 12 · answered Jul 07 '23 at 21:21

This query fixes the 1st query in the Accepted Answer if your database has custom datatypes like mine does. No more duplicate records in the output.

SELECT t.[name] AS Table_Name
       ,c.[name] AS Column_Name
       ,ut.[name] AS User_Data_Type
       ,us.[name] AS System_Data_Type
       ,CAST(us.precision AS VARCHAR) + '/' + CAST(us.scale AS VARCHAR) AS Precision_Scale
       ,c.max_length AS Size
FROM   sys.objects AS t
       INNER JOIN sys.columns c ON t.[object_id] = c.[object_id]
       INNER JOIN sys.types ut ON c.user_type_id = ut.user_type_id
       INNER JOIN sys.types us ON c.system_type_id = us.user_type_id
WHERE  t.[type_desc] = 'USER_TABLE'
ORDER BY t.[name]
        ,c.[name]

score -1 · Answer 13 · answered May 02 '17 at 06:57

-1

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

answered May 02 '17 at 06:57

Sampath Kumar S

33
6

score -1 · Answer 14 · answered Apr 14 '20 at 08:30

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.

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

14 Answers14

Linked

Related