1) HOW CAN WE FIND THE NAME OF THE PROCEDURE WHICH IS POPULATION A TABLE SAY 'MYTABLE', I HAVE ONLY THE TABLE NAME WITH ME THERE MUST BE SOME SYSTEM TABLE IN WHICH WE CAN QUERY TO FIND THE PROCEDURE NAME'
2)HOW CAN I FIND THE LIST OF ALL THE TABLES IN WHICH SAY A FIELD NAME 'MYFIELD' IS BEING USED
3)IS IS POSSIBLE TO FIND THAT IF THE TABLE IS BEING POPULATED BY A PROCEDURE OR FROM SOME OTHER SOURCE (SAY ANY ETL TOOL)
IS THERE ANY TABLE THAT STORES THE META DATA OF ALL THE PROCEDURES HERE WE CAN THEN SEARCH FOR TABLE NAME (ITS JUST A SUGGESTION)
I CHECKED IN DBC.TABLES FOR TABLEKIND 'P' BUT THE METADATA HERE IS COMING IN SOME ENCRIPTED FORM I AM IN NEED OF A TABLE WHERE THE EXACT DESCRIPTION OF PROCEDURES IS STORED IN TERA DATA.. THANKS
1.1) Repeat this SQL statement: "show procedure MyProc;" for each Stored Procedure "MyProc" you can find in the system. You can't lookup the "requesttext" field of dbc.tables to get affordable results (i.e., the DDL of objects). 1.2) With "show procedure ...;" you get each of the S.P. DDL's, from which you can search for your target tablename.
2) SQL: "select databasename,tablename from dbc.columns where columnname='MYFIELD' group by 1,2;"
3) You can define such a table and populate it according to what is doing your ETL tool. I don't know if there is any automatic tool to collect that info (maybe DBQL or AccessLogging?).