CODEWITHSUNDEEP
sql-servert-sqlnames
TSQL_Newbie
TSQL_Newbie

Reputation: 821

Extract table names from TSQL Query

I have extracted some auto generated TSQL from a program and within the FROM section of the query there are a million (slightly exaggerated) JOINS to various tables.

Lots are repeats but with different aliases e.g. table1, table1_1, table1_2 etc etc. Is there a way or tool that will just list me the tables names at an aggregated level so I can see exactly which tables are being used? e.g. in the above example I would just want to see table1 listed.

Upvotes: 0

Views: 901

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46203

Use sp_describe_first_result_set with t@browse_information_mode = 1 to return query meta-data like the example below. To get aggregated information, insert these results into a temp table or table variable and query to get the info you need. The result table schema is described in the Books Online (http://msdn.microsoft.com/en-us/library/ff878602.aspx).

EXEC sp_describe_first_result_set 
    @tsql = N'
        SELECT t1.column_1, c.name
        FROM dbo.table_1 AS t1
        JOIN dbo.table_2 AS t2 ON
            t2.column_1 = t1.column_1'
    ,@params = NULL
    ,@browse_information_mode = 1;

Upvotes: 3

Related Questions