none
如何在sqlserver 2000中查询出存储过程关联了什么表 RRS feed

  • 问题

  • 我使用SELECT OBJECT_NAME(id),OBJECT_NAME(depid) FROM sysdepends WHERE id=OBJECT_ID('GetLotWaferChipForLotList')查询出来,发现结果很奇怪,有些没有,有些出现几次。
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年9月19日 7:00

全部回复

  • Did you compare results with output of sp_depends?
    2011年9月19日 14:20
  • Did you compare results with output of sp_depends?

    It is the same as result from sysdepends.
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年9月20日 0:40
  • 有人建议说使用sp_msdependencies ,但是执行发现结果仍然一样。

    还有人说使用sp_refreshsqlmodule 更新下metadata,因为这是sqlserver2000,所以将这个数据库恢复到sqlserver2008上,之后再执行这个存储过程,执行结束之后,不管执行sp_depends还是sp_msdependencies 结果仍然一样。

    难道真的无解吗?


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年9月20日 1:36
  • How do you know results are wrong? Can you post results?
    2011年9月20日 1:53
  • How do you know results are wrong? Can you post results?


    这是执行sp_depends的结果。

    dbo.Template user table no no TemplateID
    dbo.metType user table no no TypeName
    dbo.metType user table no no TypeEnum
    dbo.metType user table no no TypeText

     

    这是这个存储过程的脚本。

    SET QUOTED_IDENTIFIER ON
    SET ANSI_NULLS ON
    GO

     

     

     


    -- ===================================================================================================
    --
    -- Notes: querytype is
    --
    --
    -- 1: metrology
    -- 2: test
    -- 3: reliablity
    -- 4: fab qual
    --
    --
    -- 11/10/05 b. Add new VOA Templates
    -- 08/08/07 b. Added another parameter @returnAsTables which is option to return results
    --     in temp tables instead as query results. This is used by Metro2 feature that opens
    --     a query that was saved in UserQuery table.
    -- 04/13/07 b. Include triplexer.
    -- 04/19/07 b. Include SVT.
    -- 01/14/08 QN Include Splitter detail and Summary GetTemplateListForQuery 1
    --===================================================================================================
    CREATE PROCEDURE [dbo].[GetTemplateListForQuery]
     @queryType smallint = 0,
     @returnAsTables bit = 0
    AS

     SET NOCOUNT ON

     CREATE TABLE #t_metQuery (
      RowIndex int identity NOT NULL ,
      [DisplayOrder] [int] NOT NULL ,
      [QueryName] [varchar] (50)   NULL ,
      [QueryLabel] [varchar] (50)   NOT NULL ,
      [QueryDescription] [varchar] (50)   NULL ,
      [QueryForm] [varchar] (50)   NULL ,
      [TestTemplateID] [int] NULL ,
      [TemplateID] [int] NULL ,
      [ParentTestTemplateID] [int] NULL ,
      [IsNonStandard] [bit] NULL ,
      [QueryType] [smallint] NULL ,
      [OnServer] [bit] NULL ,
      [IsInactive] [bit] NULL ,
      [IsWrite] [bit] NULL ,
      IsHidden bit NULL
     )


     CREATE TABLE #t_TemplateColumn ( RowIndex int IDENTITY (1, 1) NOT NULL,
      [TemplateColumnID] [int] NOT NULL ,
      [TemplateID] [int] NULL ,
      [ColIsActive] [bit] NULL ,
      [ColNameInTable] [varchar] (50)  NULL ,
      [ColFormulaSQL] [varchar] (500)  NULL ,
      [ColLabel] [varchar] (50)  NULL ,
      [ColOrder] [smallint] NULL ,
      [ColFormula] [varchar] (255)  NULL ,
      [ColDisplay] [varchar] (50)  NULL ,
      [ColType] [smallint] NULL ,
      [ColDataType] [smallint] NULL ,
      [ColIsTag] [bit] NULL ,
      [InDataOrder] [smallint] NULL
      )

    IF (@queryType = 1)
    BEGIN
     insert #t_metQuery
     (
        DisplayOrder,
        QueryName,
        QueryLabel,
        QueryDescription,
        QueryForm,
        TestTemplateID,
        TemplateID,
        IsHidden,
        ParentTestTemplateID,
        IsNonStandard,
        OnServer,
        IsWrite
     )
      SELECT
       metQuery.DisplayOrder,
       metQuery.QueryName,
       metQuery.QueryLabel,
       metQuery.QueryDescription,
       metQuery.QueryForm,
       metQuery.TestTemplateID,
       metQuery.TemplateID,
       0 ,      -- IsHidden
       metQuery.ParentTestTemplateID,
       metQuery.IsNonStandard,
       metQuery.OnServer,
       metQuery.IsWrite
      FROM metQuery 
      WHERE FIS = 1
      AND QueryType = @queryType
      AND IsInactive = 0
      ORDER BY DisplayOrder

     select RowIndex,
       DisplayOrder,
        QueryName,
        QueryLabel,
        QueryDescription,
        QueryForm,
        TestTemplateID,
        TemplateID,
        IsHidden,
        ParentTestTemplateID,
        IsNonStandard,
        OnServer,
        IsWrite
      FROM #t_metQuery
      ORDER BY RowIndex

     SELECT DISTINCT
     [TemplateColumnID] ,
     tc.[TemplateID] ,
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
     FROM TemplateColumn tc JOIN #t_metQuery t on tc.TemplateID = t.TestTemplateID
     WHERE tc.TEmplateID > 0 AND tc.ColIsActive = 1      
     ORDER BY tc.TemplateID, ColOrder

     RETURN
    END


     insert #t_metQuery
    (
       DisplayOrder,
       QueryName,
       QueryLabel,
       QueryDescription,
       QueryForm,
       TestTemplateID,
       TemplateID,
       IsHidden,
       ParentTestTemplateID,
       IsNonStandard,
       OnServer,
       IsWrite
    )
     SELECT
      metQuery.DisplayOrder,
      metQuery.QueryName,
      metQuery.QueryLabel,
      metQuery.QueryDescription,
      metQuery.QueryForm,
      metQuery.TestTemplateID,
      metQuery.TemplateID,
      0 ,      -- IsHidden
      metQuery.ParentTestTemplateID,
      metQuery.IsNonStandard,
      metQuery.OnServer,
      metQuery.IsWrite
     FROM metQuery
     
     WHERE (((metQuery.QueryType)=@queryType) AND ((metQuery.IsInactive)=0))

     -- just get ones that have columns in TemplateColumn
     AND ( EXISTS (SELECT * FROM TemplateColumn WHERE TemplateID = metQuery.TemplateID AND TemplateID > 0) OR
      EXISTS (SELECT * FROM TemplateColumn WHERE TemplateID = metQuery.TestTemplateID  AND TemplateID > 0))
    -- b. 11/10/05
    -- and (queryname like '%sws%')
    -- and (queryname like '%sws%' or queryname in ('TemplateVOADetail','TemplateVOASummary'))

     -- 04/13/07
     and (queryname like '%sws%' or queryname in ('TemplateVOADetail','TemplateVOASummary','TemplateQueryForSpec','TemplateAWG_Atune','TemplatePDACCDetail'
    ,'TemplatePDACCSummary')
      or queryname like '%tplx%' or queryname like '%svt%' or queryname like 'TemplatePwrSplitter%'
    or querylabel like '%DQPSK%'or querylabel like 'Test TAP%' or querylabel like 'Test Return%' or querylabel LIKE '%PMD%')


     ORDER BY DisplayOrder

     --  KLUGE to get 'all columns' SWS Queries

     DECLARE @dummyTemplateID int
     select @dummyTemplateID = max(templateid) + 1 from Template
     UPDATE #t_metQuery
     SET TestTemplateID = @dummyTemplateID
     WHERE QueryName = 'TemplateSWS_ALL'


     UPDATE #t_metQuery
     SET TestTemplateID = @dummyTemplateID + 1
     WHERE QueryName = 'TemplateSWS_ALLSummary'

     --Added by Haitao 2010-05-10
     UPDATE #t_metQuery
     SET TestTemplateID = @dummyTemplateID + 2
     WHERE QueryName = 'TemplateSWS_ALLSummaryBestRepeat'

     IF @returnAsTables = 0
      select RowIndex,
       DisplayOrder,
        QueryName,
        QueryLabel,
        QueryDescription,
        QueryForm,
        TestTemplateID,
        TemplateID,
        IsHidden,
        ParentTestTemplateID,
        IsNonStandard,
        OnServer,
        IsWrite
      FROM #t_metQuery
      ORDER BY RowIndex
     ELSE
      INSERT #t_metQuery_Save
      SELECT *
      FROM #t_metQuery
      ORDER BY RowIndex

     

     

     


     --
     -- Get all standard template columns.
     --
     INSERT #t_TemplateColumn
    (
     [TemplateColumnID] ,
     [TemplateID] ,
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
    )
     SELECT DISTINCT     -- testtemplateids are not unique in metquery
     [TemplateColumnID] ,
     tc.[TemplateID] ,
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
     FROM TemplateColumn tc JOIN #t_metQuery t on tc.TemplateID = t.TestTemplateID
     WHERE tc.TEmplateID > 0        
     ORDER BY tc.TemplateID, ColOrder

     


     -- get the special SWS_ALL (detail) columns
     INSERT #t_TemplateColumn
    (
     [TemplateColumnID] ,
     [TemplateID] ,
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
    )
     SELECT       
     [TemplateColumnID] ,
     @dummyTemplateID ,
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
     FROM TemplateColumn tc  JOIN Template t ON tc.TemplateID = t.TemplateID
     WHERE t.TemplateName LIKE 'SWS_%' AND TemplateName <> 'SWS_ExtractParms'
     AND TemplateName NOT LIKE '%Summary'
     AND TemplateName <> 'SWS_Coupler'
     AND tc.TemplateID in (select TestTemplateID FROM #t_metQuery)
     ORDER BY t.TemplateID, ColOrder

     INSERT #t_TemplateColumn
    (
     [TemplateColumnID] ,
     [TemplateID] ,
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
    )
     SELECT
     [TemplateColumnID] ,
     @dummyTemplateID + 1 ,   -- the summary dummy id    
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
     FROM TemplateColumn tc  JOIN Template t ON tc.TemplateID = t.TemplateID
     WHERE t.TemplateName LIKE 'SWS_%Summary'
     AND tc.TemplateID in (select TestTemplateID FROM #t_metQuery)
     ORDER BY t.TemplateID, ColOrder

    --Added by Haitao 2010-05-10
    INSERT #t_TemplateColumn
    (
     [TemplateColumnID] ,
     [TemplateID] ,
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
    )
     SELECT
     [TemplateColumnID] ,
     @dummyTemplateID + 2 ,   -- the summary dummy id    
     [ColIsActive],
     [ColNameInTable],
     [ColFormulaSQL] ,
     [ColLabel] ,
     [ColOrder] ,
     [ColFormula] ,
     [ColDisplay] ,
     [ColType] ,
     [ColDataType] ,
     [ColIsTag] ,
     [InDataOrder]
     FROM TemplateColumn tc  JOIN Template t ON tc.TemplateID = t.TemplateID
     WHERE t.TemplateName LIKE 'SWS_%Summary'
     AND tc.TemplateID in (select TestTemplateID FROM #t_metQuery)
     ORDER BY t.TemplateID, ColOrder

      DECLARE @COL_IDENTIFIER smallint
      SET @COL_IDENTIFIER=(SELECT TypeEnum FROM metType WHERE TypeName ='Col' AND TypeText='Identifier')

     --
     -- Get rid of duplicate id columns - keep the first occurrence, but delete the rest.
     --
     
      select min(RowIndex) AS KeepID, ColLabel
      INTO #deleteCols
      FROM #t_TemplateColumn
     WHERE ColType = @COL_IDENTIFIER
     AND TemplateID >= @dummyTemplateID
     GROUP BY ColLabel

     DELETE #t_TemplateColumn
     FROM #deleteCols d
     WHERE d.ColLabel = #t_TemplateColumn.ColLabel
     AND ColType = @COL_IDENTIFIER
     AND #t_TemplateColumn.RowIndex > KeepID
     AND TemplateID >= @dummyTemplateID

    --  select min(TemplateColumnID) AS KeepID, ColLabel, count(*)
    --  FROM #t_TemplateColumn
    --  WHERE ColType = @COL_IDENTIFIER
    --  AND TemplateID >= @dummyTemplateID
    --  GROUP BY ColLabel


     --
     -- Detail columns not in the tables
     --
    -- DEBUG ONLY
    if 1=2
    BEGIN
     print 'OLD COLUMNS:'
     select ttc.collabel, ttc.colnameintable, tc.TemplateID, templatename  from #t_templateColumn ttc join templatecolumn tc on ttc.templatecolumnid = tc.templatecolumnid
     join template t on tc.templateid = t.templateid
     WHERE ttc.TemplateID = @dummyTemplateID + 1
     and ttc.collabel not in (select name from syscolumns where id = object_id('T_SWS_ALLSummary'))
    END

    if 1=2
    BEGIN
     print 'OLD COLUMNS - detail:'
     select ttc.collabel, ttc.colnameintable, tc.TemplateID, templatename  from #t_templateColumn ttc join templatecolumn tc on ttc.templatecolumnid = tc.templatecolumnid
     join template t on tc.templateid = t.templateid
     WHERE ttc.TemplateID = @dummyTemplateID
     and ttc.collabel not in (select s.name from syscolumns s join sysobjects o
     on s.id = o.id
     where o.name like 't_sws_all')
     order by templatename
    END
    -- END DEBUG

     IF @ReturnAsTables = 0
      SELECT RowIndex,
      [TemplateColumnID] ,
      [TemplateID] ,
      [ColLabel] 
      FROM #t_TemplateColumn
      ORDER BY RowIndex
     ELSE
      INSERT #t_TemplateColumn_Save
      SELECT  * 
      FROM #t_TemplateColumn
      ORDER BY RowIndex

     DROP TABLE #t_metQuery
     DROP TABLE #t_TemplateColumn

     

     

    GO


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年9月20日 1:59
  • How did you run sp_depends?
    2011年9月20日 2:20
  • How did you run sp_depends?

    sp_depends 'GetTemplateListForQuery'
    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.
    2011年9月20日 2:35
  • Did some tests on my sql2k server, results look ok.
    2011年9月20日 14:10