Hello,
A command in 'SQL Query Analyzer does not return expected results.
This command:
sp_depends 'lkpRate'
Returns these results:
dbo.usp_Rate_del stored procedure
dbo.usp_Rate_ins stored procedure
dbo.usp_Rate_upd stored procedure
But fails to return:
dbo.usp_Rate_sel
This command:
sp_depends 'usp_rate_sel'
Returns this result:
Object does not reference any object, and no objects reference it.
Here is the table 'lkpRate':
\\CREATE TABLE [lkpRate] (
[pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
[fkRateTypeId] [smallint] NOT NULL ,
[RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
(0),
[Rate] [smallmoney] NOT NULL ,
[rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
(0),
[rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
(0),
CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
(
[pkRateId]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
(
[fkRateTypeId]
) REFERENCES [lkpRateType] (
[pkRateTypeId]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO
//
Here is the stored procedure:
\\
CREATE PROCEDURE dbo.usp_Rate_sel
AS
SET NOCOUNT ON;
SELECT
pkRateId,
fkRateTypeId,
RateDescription,
Switch1,
Rate,
rOrd,
rHide
FROM dbo.lkpRate
GO
//
What do you make of it that Query Analyzer doesn't see the stored
procedure as belonging to the table?
Thank you,
dbuchananDependency information is maintained correctly only when objects are
(re)created in correct dependency order. If usp_rate_sel was created before
the table or if the table was later recreated, dependency info will be
incomplete. You can fix correct the dependency information by recreating
usp_rate_sel.
Hope this helps.
Dan Guzman
SQL Server MVP
"dbuchanan" <dbuchanan52@.hotmail.com> wrote in message
news:1141047719.180447.206490@.i40g2000cwc.googlegroups.com...
> Hello,
> A command in 'SQL Query Analyzer does not return expected results.
> This command:
> sp_depends 'lkpRate'
> Returns these results:
> dbo.usp_Rate_del stored procedure
> dbo.usp_Rate_ins stored procedure
> dbo.usp_Rate_upd stored procedure
> But fails to return:
> dbo.usp_Rate_sel
> This command:
> sp_depends 'usp_rate_sel'
> Returns this result:
> Object does not reference any object, and no objects reference it.
> Here is the table 'lkpRate':
> \\CREATE TABLE [lkpRate] (
> [pkRateId] [smallint] IDENTITY (1, 1) NOT NULL ,
> [fkRateTypeId] [smallint] NOT NULL ,
> [RateDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Switch1] [tinyint] NOT NULL CONSTRAINT [DF_lkpRate_switch1] DEFAULT
> (0),
> [Rate] [smallmoney] NOT NULL ,
> [rOrd] [tinyint] NOT NULL CONSTRAINT [DF_tblStartupAsst_saOrd] DEFAULT
> (0),
> [rHide] [bit] NOT NULL CONSTRAINT [DF_tblStartupAsst_saHide] DEFAULT
> (0),
> CONSTRAINT [PK_tblStartupAsst] PRIMARY KEY CLUSTERED
> (
> [pkRateId]
> ) WITH FILLFACTOR = 90 ON [PRIMARY] ,
> CONSTRAINT [FK_lkpRate_lkpRateType] FOREIGN KEY
> (
> [fkRateTypeId]
> ) REFERENCES [lkpRateType] (
> [pkRateTypeId]
> ) NOT FOR REPLICATION
> ) ON [PRIMARY]
> GO
> //
> Here is the stored procedure:
> \\
> CREATE PROCEDURE dbo.usp_Rate_sel
> AS
> SET NOCOUNT ON;
> SELECT
> pkRateId,
> fkRateTypeId,
> RateDescription,
> Switch1,
> Rate,
> rOrd,
> rHide
> FROM dbo.lkpRate
> GO
> //
> What do you make of it that Query Analyzer doesn't see the stored
> procedure as belonging to the table?
> Thank you,
> dbuchanan
>|||Dan
Thank you.
Is there any way, maybe some command that I can use, to identify those
objects that are not up to date?
'sp_depends' seems kind of worthless if information must be accounted
for 'manually' in order for the commands to work.
dbuchanan|||dbuchanan (dbuchanan52@.hotmail.com) writes:
> Is there any way, maybe some command that I can use, to identify those
> objects that are not up to date?
Not really. You could run a SELECT on sysobjects to identify procedures
that have been created before tables, but that will probably give you
too much information.
> 'sp_depends' seems kind of worthless if information must be accounted
> for 'manually' in order for the commands to work.
Yes, it is a feature or limited use. I use it quite a bit myself though,
but what I do is that I build an empty database with our build tools, so
that I know that dependencies from tables to procedures are correct.
(Dependencies from procedures are not.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Start by generating a script file in Enterprise Manager. Include all
stored procedures and views. Script as CREATE, but do NOT include the
DELETE!
Now edit the script, and change all the CREATE PROC and CREATE VIEW to
ALTER commands.
Then run the script. Run it a few times, if you want. I believe the
dependencies should be up to date.
(Personally I don't pay any attention to those dependencies, but that
is partly because I formed my habits before they introduced ALTER!)
Roy
On 27 Feb 2006 07:22:50 -0800, "dbuchanan" <dbuchanan52@.hotmail.com>
wrote:
>Dan
>Thank you.
>Is there any way, maybe some command that I can use, to identify those
>objects that are not up to date?
>'sp_depends' seems kind of worthless if information must be accounted
>for 'manually' in order for the commands to work.
>dbuchanan
