August 7, 2008 at 4:42 pm
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.
August 7, 2008 at 11:41 pm
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.
August 8, 2008 at 12:31 am
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
August 8, 2008 at 9:20 am
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.
August 8, 2008 at 3:13 pm
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
August 11, 2008 at 12:24 am
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
August 11, 2008 at 8:58 am
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.
August 11, 2008 at 9:37 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 12, 2008 at 1:09 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply