由买买提看人间百态

boards

本页内容为未名空间相应帖子的节选和存档,一周内的贴子最多显示50字,超过一周显示500字 访问原贴
Database版 - SQL SERVER 面试题, find the objects
相关主题
SQL server 2000有hidden records吗?问个SQL问题
2 SQL SERVER Sr SQL Programmer positions (转载)data transfer
help about SQL for ACCESSSQL中怎样用定制列排序?
ask for help with a simple query!!!服了,这就是我们QA和DBA的水平
请问T-SQL中Group By之后怎么找到特定的record问个SQL问题- partial outer join
问一个关于SQL的问题SQL find distinct values in large table
Interesting Data Manipulation questionSQL run a stored procedure by fetching from a cursor row by row
菜鸟问.asp 里的select语句在基于SQL sever和Access语法上的不SQL select one value column for each distinct value another (转载)
相关话题的讨论汇总
话题: objects话题: new话题: tt话题: table话题: sql
进入Database版参与讨论
1 (共1页)
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
3
EXEC sp_depends 'TT'
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.

1 (共1页)
进入Database版参与讨论
相关主题
SQL select one value column for each distinct value another (转载)请问T-SQL中Group By之后怎么找到特定的record
被这题折磨了一个晚上,有人能指点下么。。问一个关于SQL的问题
SQL求助Interesting Data Manipulation question
PL/SQL, stored p, trigger etc..菜鸟问.asp 里的select语句在基于SQL sever和Access语法上的不
SQL server 2000有hidden records吗?问个SQL问题
2 SQL SERVER Sr SQL Programmer positions (转载)data transfer
help about SQL for ACCESSSQL中怎样用定制列排序?
ask for help with a simple query!!!服了,这就是我们QA和DBA的水平
相关话题的讨论汇总
话题: objects话题: new话题: tt话题: table话题: sql