CODEWITHSUNDEEP
sql
Jan
Jan

Reputation: 73

How to get all tables and column names in SQL?

I want a list of table names and columns within that table (see below). Is there a SQL query that would allow me to do this within a schema?
Result I want
I know I can look at the GUI interface to look at the table names and columns but there are too many to look at manually.

Upvotes: 7

Views: 12695

Answers (2)

Ari
Ari

Reputation: 337

however your question isn't enough clear but you can get all of it with this this code

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Upvotes: 11

David Breen
David Breen

Reputation: 247

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;

Taken from this answer: Getting list of tables, and fields in each, in a database

Upvotes: 5

Related Questions