Get list of all the tables used in multiple SQL scripts

This will only work for T-SQL coded objects (views, stored procedures, etc.)

If you have access to a SQL Server instance where you can ensure that nothing is running on it, then what I'm thinking is to run DBCC FREEPROCCACHE to drop all queries from the plan cache. Then run all of the scripts, and finally query the plan cache to get the tables. A little bit of XQuery will get the tables involved in the queries. However, this will also get queries that SQL runs behind the scenes. You might be able to filter out by a database.

Do you need to have this list of tables per script? Or just an overall all encompassing list of unique tables used by all of the scripts.

Create a new, work db. Create 275 procs from the existing code. Hopefully that's as as adding "CREATE PROCEDURE proc_NNN AS " to the beginning of the code and running it. Then use view sys.dm_sql_referencing_entities to find all objects (it doesn't seem to always list all columns, but hopefully in this case it will list them all).

I think you are confusing this with sys.dm_sql_referenced_entities. Referencing entities don't list any columns. Referenced entities only list the columns that are actually being referenced in the procedure/function.

Drew

Maybe create a database with a few synonyms and run the sql statements. You'll get failures until you have added synonyms for all references.

Thanks for all the responses.

Creating Stored Procedures for each query and use sys.dm_sql_referencing_entities is a good idea. but the problem is some queries will have variables used with out declaration (because most of the queries belong to reports and the variables get values from reports parameters). when i try to create procs, i will get errors for missing variables. I can try and write dynamic SQL to create those missing variables but it is not an easy task.

So i am planning to have a list of all the 300+ tables stored in a temp table and then a create a second temp tables for the 274 scripts and write a cursor to take each table and do a wildcard search in the query column of the 2nd temp table. and if it finds a match then insert it into a new temp table. i know this is going to take a lot of time to run, but that is least of my problems. i can leave the query running overnight if needed. how can this be done in T-SQL

following is an example scenario. 3rd table is the desired output

create table #ListOfTables (id int, tablename varchar(250))

insert into #ListOfTables values (1,'table1')

insert into #ListOfTables values (2,'table2')

insert into #ListOfTables values (3,'table3')

insert into #ListOfTables values (4,'table4')

insert into #ListOfTables values (5,'table5')

insert into #ListOfTables values (6,'table6')

insert into #ListOfTables values (7,'table7')

insert into #ListOfTables values (8,'table8')

----------------------------------------------------------

create table #ListOfQueries (Name varchar(70), Queries varchar(max))

insert into #ListOfQueries values ('Query1','SELECT *

FROM table1 INNER JOIN

table2 ON table2.id = table1.id INNER JOIN

table3 ON table3.id = table2.id INNER JOIN

table4 ON table4.id = table3.id')

insert into #ListOfQueries values ('Query2','SELECT *

FROM table1 INNER JOIN

table4 ON table4.id = table1.id ')

insert into #ListOfQueries values ('Query3','SELECT *

FROM table6 INNER JOIN

table8 ON table8.id = table6.id ')

insert into #ListOfQueries values ('Query4','SELECT *

FROM table1 ')

---------------------------------------------------------

create table #DesiredOutput (Name varchar(70), TableName varchar(250))

insert into #DesiredOutput values ('Query1','table1')

insert into #DesiredOutput values ('Query1','table2')

insert into #DesiredOutput values ('Query1','table3')

insert into #DesiredOutput values ('Query1','table4')

insert into #DesiredOutput values ('Query2','table1')

insert into #DesiredOutput values ('Query2','table4')

insert into #DesiredOutput values ('Query3','table6')

insert into #DesiredOutput values ('Query3','table8')

insert into #DesiredOutput values ('Query4','table1')

-----------------------------------------------------------

select * from #ListOfTables

select * from #ListOfQueries

select * from #DesiredOutput

----------------------------------------------------------

drop table #ListOfTables

drop table #ListOfQueries

drop table #DesiredOutput

Many Thanks.

A cursor isn't necessary. Not even sure that a table of the table names is necessary. But, based upon loading the scripts into a table, you could do this (works against the current database... sample data is designed for the master database). If the tables are in multiple databases, then you would need the table of table names to query against, but you should be able to modify this query easily to handle that.

DECLARE @Scripts TABLE (ScriptID INTEGER IDENTITY, ScriptText VARCHAR(MAX));

INSERT INTO @Scripts (ScriptText)

VALUES ('SELECT * FROM spt_fallback_db'), -- one table in script

('SELECT * FROM spt_fallback_dev, spt_fallback_db'), -- two tables in the script

('SELECT * FROM spt_fallback_usg'),

('SELECT * FROM spt_monitor'),

('SELECT * FROM spt_values, spt_monitor'), -- two tables in the script

('SELECT * FROM MSreplication_options, spt_monitor, spt_values'); -- three tables in the script

WITH cte AS

(

SELECT so.schema_id, so.name, so.object_id, t1.*

FROM @Scripts t1

JOIN sys.objects so

ON t1.ScriptText LIKE '%' + so.name + '%'

)

SELECT t2.*,

ca.Tables

FROM @Scripts t2

CROSS APPLY (SELECT STUFF((SELECT ',' + name FROM cte WHERE cte.ScriptID = t2.ScriptID ORDER BY name FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')) ca(Tables);

I found another solution which works exactly as i wanted. following is the query(try it on adventure works sample DB). Now the problem with this query is that it gives tables only if the query starts with 'Select or a DB obleject like 'dbo.uspGetEmployeeManagers','EXECUTE [dbo].[uspGetEmployeeManagers] @EmployeeID=50'. it does not work for complex SQL like 'Declate @start int select * from address where contact id = @start'

SET NOCOUNT ON;

GO

----Create a sample table to simulate a table with TSQL Commands

--drop table #TSQL_Commands

CREATE TABLE #TSQL_Commands(Id INT identity,ObjName VARCHAR(100), tsql_stmt NVARCHAR(MAX));

INSERT INTO #TSQL_Commands VALUES (NULL,'SELECT *

FROM person.contact c

INNER JOIN person.[Address] ON [c].[rowguid] = [Address].[rowguid]

WHERE [ContactID] < 50 ');

INSERT INTO #TSQL_Commands VALUES (NULL,'SELECT *

FROM person.contact c

INNER JOIN person.[Address] ON [c].[rowguid] = [Address].[rowguid]');

INSERT INTO #TSQL_Commands VALUES (NULL,'SELECT *

FROM [Sales].[SalesOrderHeader] sh

INNER JOIN sales.[SalesOrderDetail] sd ON [sh].[SalesOrderID] = [sd].[SalesOrderID]

INNER JOIN sales.[SalesOrderHeaderSalesReason] ON [sd].[SalesOrderID] = [SalesOrderHeaderSalesReason].[SalesOrderID]')

INSERT INTO #TSQL_Commands VALUES ('dbo.uspGetEmployeeManagers','EXECUTE [dbo].[uspGetEmployeeManagers] @EmployeeID=50');

INSERT INTO #TSQL_Commands VALUES ('[dbo].[uspGetBillOfMaterials]','EXECUTE [dbo].[uspGetBillOfMaterials] @StartProductID=500,@CheckDate=''20090203''')

--INSERT INTO #TSQL_Commands VALUES (6,'dbo.vw_test','select * from vw_test')

GO

--Cursor to execute dynamic sql with fmtonly, so the stmt is not actually executed, but a query plan is built

DECLARE @sql NVARCHAR(MAX)

DECLARE curExecDynSQL CURSOR LOCAL STATIC FOR

SELECT tsql_stmt

FROM #TSQL_Commands

OPEN curExecDynSQL

FETCH NEXT FROM curExecDynSQL INTO @sql

WHILE @@FETCH_STATUS = 0

BEGIN

--PRINT @sql

SET FMTONLY ON;

EXEC sp_executesql @sql

SET FMTONLY OFF;

FETCH NEXT FROM curExecDynSQL INTO @sql

END

CLOSE curExecDynSQL

DEALLOCATE curExecDynSQL

GO

--Cache query to extract tables names from the cached execution plan

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT

[id],

(SELECT [processing-instruction(x)]=[Stmt] FOR XML PATH(''),TYPE) AS [Stmt],

[db],

[Schema],

[Tbl],

[Alias]

FROM(

SELECT

tsql_st.id,

SUBSTRING(

st.text,

(qs.statement_start_offset/2)+1,

(

(

CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2

)

+ 1) AS Stmt,

x.i.value('(OutputList/ColumnReference/@Database)[1]', 'VARCHAR(100)')AS [db],

x.i.value('(OutputList/ColumnReference/@Schema)[1]', 'VARCHAR(100)')AS [Schema],

x.i.value('(OutputList/ColumnReference/@Table)[1]', 'VARCHAR(100)')AS [Tbl],

x.i.value('(OutputList/ColumnReference/@Alias)[1]', 'VARCHAR(100)')AS [Alias]

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text([sql_handle]) st

CROSS APPLY sys.dm_exec_query_plan([plan_handle]) qp

CROSS APPLY qp.query_plan.nodes('//RelOp') x(i)

INNER JOIN #TSQL_Commands tsql_st

ON tsql_st.[tsql_stmt] COLLATE SQL_Latin1_General_CP1_CI_AS =

SUBSTRING(st.text,(qs.statement_start_offset/2)+1,((

CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(st.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+ 1)

OR st.[objectid] = object_id(tsql_st.ObjName)

WHERE

x.i.value('@PhysicalOp', 'NVARCHAR(200)') IN('Table Scan','Index Scan','Clustered Index Scan','Index Seek','Clustered Index Seek')

AND EXISTS(

SELECT 1

FROM sys.tables t

WHERE

t.name = REPLACE(REPLACE(x.i.value('(OutputList/ColumnReference/@Table)[1]', 'VARCHAR(100)'),'[',''),']','')

AND T.TYPE = 'U'

AND T.is_ms_shipped = 0

)

) AS x

GROUP BY

[id],

[db],

[Schema],

[Tbl],

[Alias],

[Stmt]

ORDER BY [id] ASC

drop table #TSQL_Commands