Is it possible to get all the fields from all tables?

1

Hello
I want to produce a list of all the fields in all the tables in the SQL server, in the format:

TableName, FieldName

Is there a SQL statement that can return this so that I don't have to query each manually?

Thanks!

2 
Select t.name, c.name 
From sys.tables t 
Join sys.columns c 
on t.object_id = c.object_id
3

Thanks! However, it did not pick up the right (or all) the tables. None of the tables under Views of a specific db directory was included. Any hint?

4

Use dbname
Go

What db name are you running it under?

5 
SELECT o.name AS table_or_view_name, c.name AS column_name
FROM sys.objects o
INNER JOIN sys.columns c ON c.object_id = o.object_id
WHERE o.type_desc IN ('USER_TABLE', 'VIEW')
ORDER BY 1, c.column_id
1 Like
6

That is brilliant many thanks! Can you explain a bit the code please? Which are the built-in variables and what they represent? Also, how can I limit the search in a specific db?

Thanks

7

The views are described in MS's documentation. Google "sys.objects" and "sys.columns" to look up further details.

You can limit the search in a specific db by adjusting the WHERE clause to add whatever limiting conditions you want.

8

hi

you can use
information_schema.columns

select table_name , column_name from information_schema.columns

please see the below link
https://www.mssqltips.com/sqlservertutorial/183/informationschemacolumns/

1 Like
9

Don't use information_schema views. They are slow, error prone (for example, they could report the wrong schema name) and do not contain all metadata that is available from the system standard views.

10

how many dbs do you want to cover? all of them?
so all dbs, all tables and all views? Not sure why you want to view details of the view columns as they are based on underlying tables

11

Yes all of them would be great.

I am not clean on what is the difference between views and tables so it would be good if I have everything.

Can you help me with some code to list all the tables/views, with all their respective columns and all their respective distinct values?

E.g:
Table1, Col1, Value1
Table1, Col1, Value2
etc

Thanks!

12

why do you need that? what is the purpose of this effort?

13

I need to have a complete map of tables, fields and their respective possible values so that I can do correct mapping between GUI fields and SQL table fields for a system. Sometimes, the names of the fields are not matching properly so I can use their distinct values to do the mapping.

Thanks!

14 
DECLARE @command varchar(max) 
SELECT @command = 'USE ? SELECT DB_NAME() as db, o.name AS table_or_view_name, 
c.name AS column_name, ts.name, ts.is_nullable, ts.max_length
FROM sys.objects o
INNER JOIN sys.columns c ON c.object_id = o.object_id
inner join sys.types ts on ts.system_type_id = c.system_type_id
WHERE o.type_desc IN (''USER_TABLE'', ''VIEW'')
ORDER BY 1, c.column_id' 

EXEC sp_MSforeachdb @command
15

Thanks! But is this complete? Because I get:
Could not find stored procedure 'p_MSforeachdb'.

16

you have a typo

it should be
sp_MSforeachdb

s
is missing