Write a query to list all the tables and columns used in a T-SQL code.

Question

Write a query to list all the tables and columns used in a T-SQL code.

SQL DBA 0

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-17T21:22:32.4166667+00:00

This is non-trivial. Can you be more specific what your needs are? (Most likely, there will have to be compromises.)

3 answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-17T21:22:32.4166667+00:00

This is non-trivial. Can you be more specific what your needs are? (Most likely, there will have to be compromises.)

Answer 1

Wilko van de Velde 2,236

If you also want to know in which database and schema the tables and columns are, you can use:

SELECT  
		DB_NAME() as [Database_Name],
		SCHEMA_NAME(schema_id) as [Schema_Name],
		t.name AS Table_Name, 
		c.name AS Column_Name
FROM	sys.columns AS c
INNER JOIN 
		sys.tables AS t 
ON		t.object_id = c.object_id
ORDER BY 
		DB_NAME(),
		SCHEMA_NAME(schema_id),
		t.name, 
		c.name;

Answer 2

Guoxiong 8,206

Here is the query to list the table names and their column names:

SELECT t.name AS Table_Name, c.name AS Column_Name
FROM sys.columns AS c
INNER JOIN sys.tables AS t ON t.object_id = c.object_id
ORDER BY t.name, c.name;

Gary Morgan 0

I was looking for something similar to this and took your query and added the sys.types table to include column types. thanks

SELECT
t.name AS Table_Name, c.name AS Column_Name, ty.name as Column_Type, 
ty.max_length as Column_Max_length, ty.precision as Column_Precision, ty.scale as Column_Scale
FROM
sys.columns AS c 
INNER JOIN sys.tables AS t ON t.object_id = c.object_id
INNER JOIN sys.types ty on c.user_type_id = ty.user_type_id
ORDER BY
t.name, c.name;

Answer 3

Hi @SQL DBA

Write a query to list all the tables and columns used in a T-SQL code.

If you're looking for a generic query for columns and tables used in different T-SQL code, then as far as I know, this is not possible.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Write a query to list all the tables and columns used in a T-SQL code.

3 answers

Your answer