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
>
>.
>

How do I generate auto increment number in SQL Express?

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?Thanks a lot.For your first question: In SQL Server, you can use data type int as your identity column and assign this column as indentity field from the property window. It will work like autonumber field in Access.|||

cckiat:

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)? Thanks a lot.

IDENTITY is the auto increament in SQL Server it is a property of the column, the second one you described is a SEQUENCE it is in Oracle not SQL Server but it is similar to IDENTITY. Both are defined by ANSI SQL but Microsoft and Oracle choose to implement one and not the other. But you can use GUID in SQL Server to generate Unique numbers but it is a 16bytes Binary data type so use it with care. Hope this helps.