documenting dependencys

  • Does anyone have a tool or methodology to document dependencies. I'm not talking about objects within the database as SLQ Server does a semi okay job of this. (although I have seen dependencies that did not display when "View Dependencies" was selected).

    What I'm talking about is all the crystal reports and applications that call views within the database. In order to avoid disaster you should know every external production system that is using a specific view or stored procedure or for sure you are going to want to modify one and break something. Would love any feedback on this. Thanks.

  • i am not sure about the availability of tool, but you can run a task in sql profiler to capture all the activities on the server. Then you can drill the information like which objects are used and from where.

    but note, running sql profiler at server level can have performance issues.

    For sure there would be better ways to do it. I hope experts will give their opinion and advice.

  • Here are some thoughts which we are planning to implement at our end...

    1. Devide Application in to isolated modules which you will have by default.

    2. Every View/SP created/altered in the application would be part of some module, so every change in Database objects can be logged into a seperate table. This table will have

    Change_Request_Log --table name

    (ID - ID of table

    Change_Request_Number - I am guessing that your company must be maintaing

    each change in the application with Change request Number.

    Module_name - Application Module Name.

    Object name - Stored Procedure/View Name.

    created Date - Date of creation.

    Modified date) - Date of Modification.

    Now,For each change in DB object prepare a template script which will insert a record here

    something like

    ---Template 1.0

    IF NOT EXISTS (Select 1 from Change_Request_Log

    where Change_Request_Number =123

    and ModuleName='ABC'

    and [Object_Name]='usp_AnySP')

    --here comes your script

    EXEC ('CREATE VIEW......') ---Create/Alter statement needs to be first statement of the batch

    run this script from SQLCMD or OSQL prompt, remember values 123,'ABC','usp_AnySP' will come as scripting variables.

    Please let me know if you have any questions...

    Regards
    Shrikant Kulkarni

  • Thanks for your reply. I'm still digesting and maybe I don't really understand. Does this solve the problem in knowing that for example: report a and b are using viewab. Report a wanted a little different tweak but I don't remember that report b is also calling this view. I make a change that has the potential of breaking reportb.

    I go between having a unique view or sp for each application or report and the preferred "code reuse" where reports or applications can call common views. There is simplicity is the one for one model but then you have a whole different maintenance problem.

    I use views alot because of the ability to secure the underlying tables easily.

  • I've seen an ad for a tool that would go through .NET code and document dependencies, but I seriously doubt there's anything that can go through your whole system and document everything for you.

    Even within SQL Server, you don't have native dependency documentation for cross-database or cross-server code. Not really.

    Possibly a good way to do this kind of thing would be to add extended properties to your views and procs, indicating which applications/reports/whatever use them. That will require some work, but it's probably your best bet.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thank you.

    dherman (8/8/2008)


    Thanks for your reply. I'm still digesting and maybe I don't really understand. Does this solve the problem in knowing that for example: report a and b are using viewab. Report a wanted a little different tweak but I don't remember that report b is also calling this view. I make a change that has the potential of breaking reportb.

    I go between having a unique view or sp for each application or report and the preferred "code reuse" where reports or applications can call common views. There is simplicity is the one for one model but then you have a whole different maintenance problem.

    I use views alot because of the ability to secure the underlying tables easily.

    The steps I wrote are at very basic level that one can follow, if he wants to track obects developed in database and logical components of application. I don't have full proof plan at this moment, I stared thinking about it 2-4 months back and then left midway, This project was not given much importance as it should have been. Anyways, we can make it better since you have requirements now.

    Yes, Object sharing across modules is usual in all applications moreover

    Same/different Change_Request can create/modify objects of many different modules which are shared at application level. I think there should not be any problems if table has redudent data since we hardly expect 1000-10000 rows here. I mean Object_Name would be unique at any point of time so script will be either CREATE/ALTER for a perticular Change Request Number.

    Please let me know if you have anymore scenarios we can discuss and sort it out.

    Regards
    Shrikant Kulkarni

  • What do you think of this idea. I know it is really crazy but I'm going to throw it out there. I've looked at the redgate dependency tracker. It is pretty slick. What if you created a documentation database. In this database you created... fake stored procs named with the same name as your external app or Crystal report. Within the stored proc you just did a simple select or exec of all the views or sp the application called.

    With this, Redgate would actually find them all.

    Probably should write the sp so it couldn't possibly run. Very unconventional but it might work.

  • Lots of things will work, but unless your developers can continue to update them and follow the standards, things will have problems.

    This is really more of an administrative issue than anything else. If people included comments in their code as to what was being called, likely you could just run a search in the VCS for a proc to know if it's being used.

  • Just a suggestion, if you do not have too many SPs you can have the database tell you what I believe you want to know.

    Remeber it is work intensive on your part particularly when preforming the 2nd step

    First thing of course is to determine the names or the SPs and the Application name that is using the SP.

    1. Create a table to hold this information

    2. Modify each existing SP that you feel that might be used by more than one application to execute dbo.PerformanceStats_Update

    3. After a perod of time review the data in the PerformanceStats table and make your decisions.

    4. If you desire to cease collecting data simply rename the table "PerformanceStats"

    Here is the code I have used (in SQL 2000) to solve just the problem you are having, I have no reason to believe it will not work in 2005 (Good luck)

    USE [Test] /* my database for testing before placing in production */

    GO

    /****** Object: Table [dbo].[PerformanceStats] Script Date: 08/12/2008 14:51:11 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PerformanceStats]') AND type in (N'U'))

    BEGIN

    CREATE TABLE [dbo].[PerformanceStats](

    [RecNo] [int] IDENTITY(1,1) NOT NULL,

    [ProcName] [varchar](100) NOT NULL,

    [ProcStart] [datetime] NOT NULL CONSTRAINT [DF_PerformanceStats_ProcStart] DEFAULT (getdate()),

    [ProcFinish] [datetime] NOT NULL CONSTRAINT [DF_PerformanceStats_ProcFinish] DEFAULT (getdate()),

    [UserName] [varchar](100) NOT NULL CONSTRAINT [DF_PerformanceStats_UserName] DEFAULT (rtrim(suser_sname())),

    [MachineName] [varchar](100) NOT NULL CONSTRAINT [DF_PerformanceStats_MachineName] DEFAULT (rtrim(host_name())),

    [ApplicationName] [varchar](100) NOT NULL CONSTRAINT [DF_PerformanceStats_ApplicationName] DEFAULT (app_name()),

    [Counter] [bigint] NOT NULL CONSTRAINT [DF_PerformanceStats_Counter] DEFAULT ((0)),

    CONSTRAINT [PK_PerformanceStats] PRIMARY KEY CLUSTERED

    (

    [RecNo] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

    CONSTRAINT [IX_PerformanceStats_1] UNIQUE NONCLUSTERED

    (

    [ProcName] ASC,

    [ApplicationName] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    END

    GO

    SET ANSI_PADDING OFF

    GO

    /****** Object: Index [IX_PerformanceStats] Script Date: 08/12/2008 14:51:12 ******/

    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PerformanceStats]') AND name = N'IX_PerformanceStats')

    CREATE NONCLUSTERED INDEX [IX_PerformanceStats] ON [dbo].[PerformanceStats]

    (

    [RecNo] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'PerformanceStats', N'COLUMN',N'RecNo'))

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=NULL , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PerformanceStats', @level2type=N'COLUMN',@level2name=N'RecNo'

    GO

    IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'PerformanceStats', N'COLUMN',N'ProcName'))

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The stored procedure being executec' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PerformanceStats', @level2type=N'COLUMN',@level2name=N'ProcName'

    GO

    IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'PerformanceStats', N'COLUMN',N'ProcStart'))

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Set only when first row entered' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PerformanceStats', @level2type=N'COLUMN',@level2name=N'ProcStart'

    GO

    IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'PerformanceStats', N'COLUMN',N'ProcFinish'))

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Updated each time proc is executed' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PerformanceStats', @level2type=N'COLUMN',@level2name=N'ProcFinish'

    GO

    IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'PerformanceStats', N'COLUMN',N'Counter'))

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'How many times executed between ProcStart and ProcFinish times' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'PerformanceStats', @level2type=N'COLUMN',@level2name=N'Counter'

    GO

    /****** Object: StoredProcedure [dbo].[PerformanceStats_Update] Script Date: 08/12/2008 14:51:12 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PerformanceStats_Update]') AND type in (N'P', N'PC'))

    BEGIN

    EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[PerformanceStats_Update]

    /*

    @Procid is passed from the calling procedure which uses @@PROCID to determine its value

    or modify this procedure to use the procedure name passing it from the calling proceedure

    using:

    SET @ProcName = OBJECT_NAME(@@PROCID);

    */

    @Procid INT

    AS

    DECLARE @Counter Int

    DECLARE @NewCount Int

    SET @NewCount = 1

    IF EXISTS (select xtype from dbo.sysobjects WHERE Id = OBJECT_ID(''PerformanceStats''))

    Begin

    SELECT @Counter = (SELECT counter

    FROM PerformanceStats

    WHERE ApplicationName = RTRIM(App_Name()) AND ProcName = RTRIM(object_name(@procid)))

    If ISNUMERIC(@counter) = 0

    BEGIN

    INSERT INTO PerformanceStats (ProcName, Counter)

    VALUES (RTRIM(object_name(@procid)), @NewCount)

    END

    ELSE

    BEGIN

    SET @NewCount = @Counter + 1

    UPDATE PerformanceStats

    Set Counter = @NewCount, ProcFinish = GetDate()

    WHERE ApplicationName = RTRIM(App_name()) AND ProcName = RTRIM(object_name(@procid))

    END

    END'

    END

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply