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

SQL DBA 0 Reputation points
2023-07-17T20:25:12.84+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,250 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2023-07-17T22:03:26.5633333+00:00

    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;
    
    0 comments No comments

  2. CosmogHong-MSFT 19,906 Reputation points Microsoft Vendor
    2023-07-18T02:43:51.5766667+00:00

    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.

    0 comments No comments

  3. Wilko van de Velde 2,146 Reputation points
    2023-07-18T07:43:07.4933333+00:00

    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;
    
    0 comments No comments