How do I identify dependencies before adding a column to a table?

I need to add a new column to a frequently used table in Production.
I want to make sure that the addition of the column does not break any
existing code in stored procs or triggers.
I can run sp_depends to identify potential problems but I believe this
system procedure is not entirely reliable.
Is the best approach a text search of sysobjects/syscomments to identify
dependencies? And other than "SELECT *," what are some things that might
break that I should be looking for?
Thanks
Dave> And other than "SELECT *," what are some things that might
> break that I should be looking for?
Off the top of my head, there is at least one other thing. The following
will now fail with incorrect number of columns specified:
INSERT table VALUES(blah, blah, blah)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||In trigger code, the function columns_updated() is positional.
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||Sp_depends only contains info about objects that existed when you created
the sp... So it is possible that sp_depends does NOT show all sp
dependencies... I generally script out all sps and do a find...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"DaveF" <davef@.comcast.net> wrote in message
news:uNHgPrzwDHA.2784@.tk2msftngp13.phx.gbl...
> I need to add a new column to a frequently used table in Production.
> I want to make sure that the addition of the column does not break any
> existing code in stored procs or triggers.
> I can run sp_depends to identify potential problems but I believe this
> system procedure is not entirely reliable.
> Is the best approach a text search of sysobjects/syscomments to identify
> dependencies? And other than "SELECT *," what are some things that might
> break that I should be looking for?
> Thanks
> Dave
>|||> In trigger code, the function columns_updated() is positional.
True. Though for the benefit of the original poster, code using
columns_updated() would only be affected if you used Enterprise Manager to
inject the column in the middle of the table. If you use ALTER TABLE ...
ADD COLUMN, the ordinal_position is next, so that existing code for
columns_updated should not have any effect...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks to everyone who responded.
I created a stored proc to search the code for potential problems.
This approach helped me find some things that sp_depends did not.
For anyone else who may face this problem, I include the proc below.
Thanks again.
Dave
USE admin
GO
--sample call:
--EXEC checkdepends 'mls', 'select *'
--dbo.checkdepends.PRC
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[checkdepends]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[checkdepends]
GO
CREATE PROC checkdepends
@.objname varchar(128) --object on which to identify dependencies (e.g.,
table name)
,@.str varchar(60) -- string used to search for dependencies (e.g., SELECT
* )
AS
DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
,@.c int -- record counter for coursor
,@.i int -- affected records counter
SET @.c = 0
SET @.i = 0
SET @.objname = '%' + @.objname + '%'
SET @.str = '%' + @.str + '%'
SET NOCOUNT ON
--build a table to hold the list of referencing objects (i.e., any db
object that references the target object #objname)
IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
DROP TABLE #refs
CREATE TABLE #refs
(
objname varchar(128)
,objtype varchar(24)
)
--grab all of the objects that reference the target object in their code
INSERT INTO #refs
SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id WHERE
text LIKE @.objname ORDER BY xtype
SELECT @.i= count(*)
FROM #refs
PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
PRINT ''
--create a table to hold the referencing object's code
IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
DROP TABLE #text
CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
--create a cursor to examine each referencing object in detail
DECLARE curs CURSOR
FOR
SELECT objname, objtype
FROM #refs
FOR READ ONLY
OPEN curs
FETCH NEXT FROM curs
INTO @.objname, @.objtype
WHILE @.@.fetch_status = 0
BEGIN
-- Add the proc code to the table (1 line of code = 1 record)
INSERT #text
EXEC sp_helptext @.objname
--check for the presence of search string within the referencing object's
code
SELECT @.i= count(*)
FROM #text
WHERE obj_text LIKE @.str
IF @.i > 0
BEGIN
PRINT
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++'
PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
PRINT ''
-- Select from the lines of code that have the string we are looking for
SELECT *
FROM #text
WHERE obj_text LIKE @.str
--empty the table to get ready for another proc
TRUNCATE TABLE #text
END
--increment the counter
SET @.c=@.c+1
FETCH NEXT FROM curs
INTO @.objname, @.objtype
END
CLOSE curs
DEALLOCATE curs|||Note that their still might be another problem, and that is if you have a
stored procedure > 8kb, a column or table name might actually start at
character 7995 and end at character 8012, which means that the result could
be split across separate columns in syscomments.
Might be safer to script the procedures, functions etc. and do a brute force
file-based search instead of using syscomments.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"DaveF" <davef@.comcast.net> wrote in message
news:uG1MTR1wDHA.1272@.TK2MSFTNGP12.phx.gbl...
> Thanks to everyone who responded.
> I created a stored proc to search the code for potential problems.
> This approach helped me find some things that sp_depends did not.
> For anyone else who may face this problem, I include the proc below.
> Thanks again.
> Dave
>
> USE admin
> GO
> --sample call:
> --EXEC checkdepends 'mls', 'select *'
> --dbo.checkdepends.PRC
> IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id => object_id(N'[dbo].[checkdepends]')
> and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> DROP PROCEDURE [dbo].[checkdepends]
> GO
> CREATE PROC checkdepends
> @.objname varchar(128) --object on which to identify dependencies (e.g.,
> table name)
> ,@.str varchar(60) -- string used to search for dependencies (e.g.,
SELECT
> * )
> AS
> DECLARE @.objtype varchar(24) --type of object (stored proc, trigger)
> ,@.c int -- record counter for coursor
> ,@.i int -- affected records counter
> SET @.c = 0
> SET @.i = 0
> SET @.objname = '%' + @.objname + '%'
> SET @.str = '%' + @.str + '%'
> SET NOCOUNT ON
> --build a table to hold the list of referencing objects (i.e., any db
> object that references the target object #objname)
> IF OBJECT_ID('tempdb..#refs','u') IS NOT NULL
> DROP TABLE #refs
> CREATE TABLE #refs
> (
> objname varchar(128)
> ,objtype varchar(24)
> )
>
> --grab all of the objects that reference the target object in their code
> INSERT INTO #refs
> SELECT name, xtype FROM sysobjects o JOIN syscomments c ON c.id=o.id
WHERE
> text LIKE @.objname ORDER BY xtype
> SELECT @.i= count(*)
> FROM #refs
> PRINT 'There are ' + str(@.i) + ' objects referencing ' + @.objname
> PRINT ''
>
> --create a table to hold the referencing object's code
> IF OBJECT_ID('tempdb..#text','u') IS NOT NULL
> DROP TABLE #text
> CREATE TABLE #text (rowid int identity, obj_text varchar(8000))
>
> --create a cursor to examine each referencing object in detail
> DECLARE curs CURSOR
> FOR
> SELECT objname, objtype
> FROM #refs
> FOR READ ONLY
> OPEN curs
>
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> WHILE @.@.fetch_status = 0
> BEGIN
> -- Add the proc code to the table (1 line of code = 1 record)
> INSERT #text
> EXEC sp_helptext @.objname
> --check for the presence of search string within the referencing
object's
> code
> SELECT @.i= count(*)
> FROM #text
> WHERE obj_text LIKE @.str
> IF @.i > 0
> BEGIN
> PRINT
>
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++'
> PRINT str(@.c) + '. ' + @.objname + '. Type = ' + @.objtype
> PRINT ''
> -- Select from the lines of code that have the string we are looking
for
> SELECT *
> FROM #text
> WHERE obj_text LIKE @.str
> --empty the table to get ready for another proc
> TRUNCATE TABLE #text
> END
> --increment the counter
> SET @.c=@.c+1
> FETCH NEXT FROM curs
> INTO @.objname, @.objtype
> END
>
> CLOSE curs
> DEALLOCATE curs
>
>
>|||> be split across separate columns in syscomments.
Err, separate rows/tuples, sorry.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||hello Dave,
I think the answer to your problem is simple. Build it. If
you do a complete build of all your source any errors that
your change could cause will be raised. Sounds easy when I
say it like this, however if you check out www.dbghost.com
it show how this can be made very simple.
All other code (exe,dll,asp) is commonly built to find
errors due to dependancy. Database code isn't (generally).
We would like to change this and take the guess work out
of making changes to your database code.
regards,
Mark Baekdal
www.dbghost.com
>--Original Message--
>I need to add a new column to a frequently used table in
Production.
>I want to make sure that the addition of the column does
not break any
>existing code in stored procs or triggers.
>I can run sp_depends to identify potential problems but I
believe this
>system procedure is not entirely reliable.
>Is the best approach a text search of
sysobjects/syscomments to identify
>dependencies? And other than "SELECT *," what are some
things that might
>break that I should be looking for?
>Thanks
>Dave
>
>.
>