September 19, 2005 at 4:19 pm
How do I run a select command to list all views in a database with particular table / column reference. We are upgrading a software program that has two tables that had column name changes. We have hundreds of custom views and not sure how many of them might need to be updated with this new information. Is there a way to run a query and get a list of all the views that contain this particular table/column name?
For example we are looking for a column name that changed from SVC100.NAME to SVC100.CUSTNAME.
ANy help in this would be GREATLY appreciated.
September 19, 2005 at 4:26 pm
You can custom write a proc or use sp_depend "<Table Name>".
Points to remeber :
Cross database joins.
Dynamic Query.
* is the column list.
If you have written query in Jobs.
Written queries in the application instead of using views or procedure
and many more
Amit Lohia
September 19, 2005 at 5:35 pm
This gives you a list of all the views that have a column 'custname".
select a.table_name, a.column_name from information_schema.columns AS a
join information_schema.views AS v ON a.table_name = v.table_name
where a.column_name = 'custname'
September 19, 2005 at 8:37 pm
In addition to this Use the view definition field to do a like for specific table.
select a.table_name, a.column_name from information_schema.columns AS a
join information_schema.views AS v ON a.table_name = v.table_name
where a.column_name = 'columnName'
and View_Definition like '%tableName%'
If you just want the view name without knowing columns
select *
from information_schema.views
where View_Definition like '%tableName%'
September 20, 2005 at 7:58 am
OK - Enthusiast - I got this code (select a.table_name, a.column_name from information_schema.columns AS a
join information_schema.views AS v ON a.table_name = v.table_name
where a.column_name = 'custname') to work on returning the views. Is there a way to modify this determine how to return only views containing the column named name ONLY if it is from the svc00200 or svc30200 tables?
If not, the above still reduces it from many hundreds to about 100 so that will certainly be MUCH more manageable
September 20, 2005 at 9:50 am
SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE COLUMN_NAME = 'custname'
AND TABLE_NAME IN ('svc00200', 'svc30200')
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply