behavior of command in the 'SQL Query Analyzer'

27 Feb 2006 1:41 PM

dbuchanan

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

27 Feb 2006 1:49 PM

Dan Guzman

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

Show quote

"dbuchanan" <dbuchana***@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
>

27 Feb 2006 3:22 PM

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

27 Feb 2006 11:32 PM

Erland Sommarskog

dbuchanan (dbuchana***@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, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx