behavior of command in the 'SQL Query Analyzer'

Hello,
A command in 'SQL Query Analyzer does not return expected results.
This command:
sp_depends 'lkpRate'
Returns these results:
dbo.usp_Rate_delstored procedure
dbo.usp_Rate_insstored procedure
dbo.usp_Rate_updstored 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
Dependency 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.googlegr oups.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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx