new columns to be added - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

new columns to be added

Author

Topic

carumuga
Posting Yak Master

174 Posts

Posted - 2009-12-10 : 01:24:57

There is a table in the production, where the new requirement has come up to alter the table by adding up 2 new attributes.

I need to see whether the object used in the SP, view, scripts, packages(DTS/SSIS), jobs are impacted. The objects gets impacted only in case if the developer use SELECT */ INSERT with no columns specified in SP/Views.

Is there any other impacts if some one come across in SP/VIEW. Also should i need to script out the object to identify whether the object is used in the package or not?

Please let me know.

Thanks in advance.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-12-10 : 01:53:48

check this link
http://blogs.msdn.com/rafats/archive/2007/04/11/column-dependencies.aspx

try this


SELECT   DISTINCT DBObject = o.Name
		, object_name(d.referenced_major_id) as TableName
		, col_name(d.referenced_major_id, d.referenced_minor_id) as ColumnName
		, d.is_updated
        , d.is_selected
FROM     sys.objects o
         JOIN sys.sql_dependencies d
           ON d.object_id = o.object_id
WHERE    d.referenced_major_id = Object_id('tblmaster')
ORDER BY DBObject

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

carumuga
Posting Yak Master

174 Posts

Posted - 2009-12-10 : 02:20:10

Thanks. Any way I just found the dependancies by using sp_depends 'objname'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-10 : 14:01:54

quote:
Originally posted by carumuga

Thanks. Any way I just found the dependancies by using sp_depends 'objname'

will this list SSIS/DTS packages where table is used?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-10 : 14:02:25

also what about cases where table comes inside dynamic sql string?

carumuga
Posting Yak Master

174 Posts

Posted - 2009-12-11 : 00:26:47

In terms of SSIS/DTS, the object used was identified manually. Is there any other where you can copy all dts packages to .vbs files. I tried using the xp_cmdshell 'dtsrun...' exported the .dts to .vbs files but it was encrypted.

EXEC MASTER..XP_CMDSHELL 'DTSRUN.EXE /S TEST /E /N "TEST" /F "E:\DTS\TEST.BAS" /!X'.
Can u pls. tweak the query to read the data in txt format.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources