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.

Answer 1

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;

Answer 2

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.

Answer 3

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;