n********6 发帖数: 1511 | 1 Now your company is doing a new business. New data flow and process have
been designed. Existing data flow and process need to be modified. A new
column CC need to be added to Table TT. New stored procedure need to be
developed to handle column CC in Table TT.
Now your manager want to know what specific existing stored procedures,
views, triggers, functions may be affected and need to be modified.
You know some of the objects will definitely be affected. But there are over
hundreds of objects an | B*****g 发帖数: 34098 | 2 其实最简单的方法就是
select *
from dict
where table_name like '%xxxxxx%'
over
【在 n********6 的大作中提到】 : Now your company is doing a new business. New data flow and process have : been designed. Existing data flow and process need to be modified. A new : column CC need to be added to Table TT. New stored procedure need to be : developed to handle column CC in Table TT. : Now your manager want to know what specific existing stored procedures, : views, triggers, functions may be affected and need to be modified. : You know some of the objects will definitely be affected. But there are over : hundreds of objects an
| j*****n 发帖数: 1781 | | n********6 发帖数: 1511 | 4 Original table: TT(ColumnAA, ColumnBB)
New table: TT(ColumnAA, ColumnBB, ColumnCC)
Further question:
By following the answers, you can find out the related objects which used
the tableTT. However, there are still too many objects.
New business requirement: Find out the objects which use the ColumnBB in
existing logic. | j*****n 发帖数: 1781 | 5 2k5 only:
SELECT DISTINCT name, type, type_desc
FROM sys.objects
WHERE object_id IN
(SELECT object_id FROM sys.sql_dependencies A
JOIN sys.syscolumns B
ON A.object_id = B.object_id AND A.column_id = B.column_id
WHERE B.object_id = OBJECT_ID(N'tableTT')
AND B.name = N'colBB'
)
【在 n********6 的大作中提到】 : Original table: TT(ColumnAA, ColumnBB) : New table: TT(ColumnAA, ColumnBB, ColumnCC) : Further question: : By following the answers, you can find out the related objects which used : the tableTT. However, there are still too many objects. : New business requirement: Find out the objects which use the ColumnBB in : existing logic.
| n********6 发帖数: 1511 | 6 Nobody want to use
SELECT *
FROM Sysobjects o
JOIN Syscomments c ON o.id = c.id
WHERE c.text like '%ColumnXXX%'
Any advantage and disadvantage in using this?
*Comments contain all the source code of the objects.
**Anyway, Baozi will be delivered very quickly for former answers.
【在 j*****n 的大作中提到】 : 2k5 only: : SELECT DISTINCT name, type, type_desc : FROM sys.objects : WHERE object_id IN : (SELECT object_id FROM sys.sql_dependencies A : JOIN sys.syscolumns B : ON A.object_id = B.object_id AND A.column_id = B.column_id : WHERE B.object_id = OBJECT_ID(N'tableTT') : AND B.name = N'colBB' : )
| j*****n 发帖数: 1781 | 7 sure you can use this one...
however, system tables will not available for later SQL server versions.
This is good for 2000, in 2k5, the speed is low.
【在 n********6 的大作中提到】 : Nobody want to use : SELECT * : FROM Sysobjects o : JOIN Syscomments c ON o.id = c.id : WHERE c.text like '%ColumnXXX%' : Any advantage and disadvantage in using this? : *Comments contain all the source code of the objects. : **Anyway, Baozi will be delivered very quickly for former answers.
| n********6 发帖数: 1511 | 8 Baozi to jackrun and beijng has been delivered.
【在 j*****n 的大作中提到】 : sure you can use this one... : however, system tables will not available for later SQL server versions. : This is good for 2000, in 2k5, the speed is low.
|
|