myjobsinus (8/31/2015)

---

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.