Integrated database documentation using stored procedure

				
--**************************************
-- Name: Integrated database documentation using stored procedure
-- Description:This stored procedure will help you to document your database within the database. It creates/updates a table that lists all of the user tables, views and stored procedures in the database, as well as all of the views and stored procedures that reference each of those objects. There are custom fields for putting in the purpose of the object, notes, or anything else you want to document. Executing the procedure after the initial creation will: 1) update the names of the objects, if changed; 2) add new objects; 3) update the list of views and stored procedures referencing the objects; and 4) mark any objects that have been deleted. This is a great way to keep your documentation up to date, in a place where all of the database developers can find it! (Updated 06/18/2003 -- see notes in code below.)
-- By: Christina McEntire
--
-- Returns:Creates/updates a table in your database called INFO_DbObjects to store the information generated by this procedure plus any other documentation you want to add.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=699&lngWId=5--for details.--**************************************

/******************************************************************************
** Name: pr_InfoDbObjects
** Description: Set up and maintain tables in order to keep track of database structure, including user tables, stored procedures, and views
**	 Initially:
**		-- create a table to hold the info, and populate it with the object names
**		-- populate list of all stored procedures and views that reference each object
**	Update:
**		-- add new objects to the table
**		-- update object names where they've changed
** 		-- mark deleted objects as deleted
**		-- update lists of stored procedures and views that reference each object
** Inputs: None
** Results: This procedure creates/updates a table in your database called INFO_DbObjects to store the information generated by this procedure plus
**		any other documentation you want to add.
** Additional Note: The Sys Id for a table changes if the order of the columns in changed, i.e. column dragged to different position; 
** 	for this reason, we need to check for table updates on both changed SysId and changed TableName
** Other: -- To see tables, stored procedures and views separately, just set up a view for each type
**	 -- If the database is changing over time, can set up a job to run this regularly. It will keep all the database object names up-to-date, 
**		as well as the list of views and stored procedures referencing each object. Any custom-documention fields will remain untouched
**		by running this procedure
** Updated 06/17/2003 — New addition for marking objects as not deleted if they had been deleted and then re-added later. Also, error in logic updated.
** Updated 06/18/2003 to correct references to an incorrect object name introduced with the last update.
*******************************************************************************/
CREATE PROCEDURE dbo.pr_InfoDbObjects
			 @Print bit = 0	-- ** flag for whether or not to print rows. Printing is off unless specifically passed in as true (1)
AS
SET NOCOUNT ON
DECLARE	 @ObjectName	varchar(100)
		, @RefView 	varchar(100)
		, @RefProc	varchar(100)
		, @AllViews	varchar(500)
		, @AllProc	varchar(500)
		, @RowCount	int
PRINT 'Beginning update of database documentation'
--------------------------------------------------------------------------------------------------------
-- ** If table doesn't exist, create it
IF NOT EXISTS (select * from sysobjects where id = object_id(N'[dbo].[INFO_DbObjects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	PRINT ''
	PRINT 'Creating table to document database structure'
	CREATE TABLE [dbo].[INFO_DbObjects] (
		[SysId] [int] NOT NULL ,
		[ObjectName] [varchar] (50) NOT NULL ,	-- ** "U" is table, "V" is view, "P" is stored procedure
		[ObjectType] [char] (1) NOT NULL ,
		[Purpose] [varchar] (300) NULL ,		-- ** custom -- YOUR documentation
		[Notes] [varchar] (1000) NULL ,		-- ** custom -- YOUR documentation
		[RefByViews] [varchar] (500) NULL ,
		[RefByProc] [varchar] (500) NULL ,
		[RefByOther_Manual] [varchar] (300) NULL ,	-- ** custom -- use to keep track of scheduled jobs or DTS packages that reference the object (manually updated) 
		[ObjectDeletedYN] [bit] NOT NULL CONSTRAINT [DF_INFO_DbObjects_ObjectDeletedYN] DEFAULT (0) ,
		[InsertDate] [smalldatetime] NOT NULL CONSTRAINT [DF_INFO_DbObjects_InsertDate] DEFAULT (getdate()) ,
	CONSTRAINT [PK_INFO_DbObjects] PRIMARY KEY NONCLUSTERED 
	(
		[SysId]
	) WITH FILLFACTOR = 90 ON [PRIMARY] 
	) ON [PRIMARY]
END
---------------------------------------------------
ELSE	
-- ** table already exists; check for updates
BEGIN
	-- ** Determine if any of the object names or sys id's have changed
	IF EXISTS(SELECT * FROM INFO_DbObjects O INNER JOIN sysobjects SO ON O.SysId = SO.id
				WHERE O.ObjectName <> SO.name)
			OR EXISTS(SELECT * FROM INFO_DbObjects O INNER JOIN sysobjects SO ON O.ObjectName = SO.name
				WHERE O.SysId <> SO.id)
	BEGIN
		-- ** Update INFO_DbObjects with object names for those that have changed BASED ON SAME SYSID
		UPDATE O
		SET O.ObjectName = SO.name
		FROM INFO_DbObjects O
			INNER JOIN sysobjects SO
				ON O.SysId = SO.id
		WHERE O.ObjectName <> SO.name
		SELECT @RowCount = @@RowCount
		IF @RowCount > 0 
		BEGIN
			PRINT ''
			PRINT 'Updated INFO_DbObjects with changed database object names based on same SysId'
		PRINT Convert(varchar(4), @RowCount) + ' row(s) affected'
		END
		---------------------------------------------------
		-- ** Update INFO_DbObjects with sys id's for those that have changed BASED ON SAME NAME (and object type must match as well)
	
		UPDATE O
		SET O.SysId = SO.id
		FROM INFO_DbObjects O INNER JOIN sysobjects SO
			ON O.ObjectName = SO.name
				AND O.ObjectType = SO.type
		WHERE O.SysId <> SO.id
		SELECT @RowCount = @@RowCount
		IF @RowCount > 0 
		BEGIN
			PRINT ''
			PRINT 'Updated INFO_DbObjects with changed SysId based on same object name and type'
		PRINT Convert(varchar(4), @RowCount) + ' row(s) affected'
		END
	END
	---------------------------------------------------
	-- ** Mark objects as NOT deleted if they HAD been marked as deleted but now exist...
	UPDATE O
	SET	O.ObjectDeletedYN = 0
	FROM INFO_DbObjects O 
		INNER JOIN sysobjects SO 
			ON O.SysId = SO.id
	WHERE O.ObjectDeletedYN = 1
	SELECT @RowCount = @@RowCount
	IF @RowCount > 0 
	BEGIN
		PRINT ''
		PRINT 'Marked un-deleted objects as NOT deleted'
		PRINT Convert(varchar(4), @RowCount) + ' row(s) affected'
	END
	---------------------------------------------------
	-- ** Mark objects as deleted if no match (both SysId and ObjectName have been updated, so no match on SysId should cover it)
	-- ** DO NOT actually DELETE the record from the table -- the user may have info in the custom columns they don't want to lose
	UPDATE O
	SET	O.ObjectDeletedYN = 1
	FROM INFO_DbObjects O 
		LEFT OUTER JOIN sysobjects SO 
			ON O.SysId = SO.id
	WHERE SO.Id is null AND O.ObjectDeletedYN = 0
	SELECT @RowCount = @@RowCount
	IF @RowCount > 0 
	BEGIN
		PRINT ''
		PRINT 'Marked deleted objects as deleted'
		PRINT Convert(varchar(4), @RowCount) + ' row(s) affected'
	END
END
--------------------------------------------------------------------------------------------------------
-- ** Insert new object names
BEGIN
	INSERT INTO INFO_DbObjects(SysId, ObjectName, ObjectType)
	SELECT Distinct 	 SO.id
				, SO.Name
				, SO.type
	FROM sysobjects SO 
		LEFT OUTER JOIN INFO_DbObjects O
			ON SO.id = O.SysId
	WHERE
		((SO.type = 'u'
		AND SO.Name NOT LIKE 'INFO[_]%')	-- ** (database "Info" table created by this procedure) 
			OR (SO.type = 'p')
			OR (SO.type = 'v')
		)
		AND SO.Category <> 2	-- ** rules out system objects
		AND SO.name <> 'dtproperties'
		AND O.SysId is null	-- ** object not yet in our table
		-- ** ADD any other custom criteria for other objects you want to include or exclude...
	ORDER BY SO.type, SO.Name
	SELECT @RowCount = @@RowCount
	IF @RowCount > 0 
	BEGIN
		PRINT ''
		PRINT 'Inserting names of new objects'
		PRINT Convert(varchar(4), @RowCount) + ' row(s) affected'
	END
END
------------------------------------------------------------------------------------------------------------------
-- ** Find all views and procedures that reference each object
BEGIN
		-- ** NOTE: We're using the "referenced by" rather than using sp_depends -- this is more accurate because it will also include objects referenced
		-- ** 	in stored procedures where an EXEC(@Statement) or similar is used, where the SQL string is built piece by piece, etc.
	PRINT ''
	PRINT 'Beginning update of views and procedures that reference each object'
	-- ** initialize @AllViews & @AllProc as empty, not null
	SELECT @AllViews = ''	
		, @AllProc = ''
	
	-- ** get the first object name from INFO_DbObjects
	SELECT @ObjectName = MIN(ObjectName) FROM INFO_DbObjects WHERE ObjectDeletedYN = 0
	
	WHILE @ObjectName is not null
	BEGIN
		-- ** Find Views that reference each table
		SELECT @RefView = MIN(so.name) 
			FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
			WHERE so.type = 'V' AND sc.text LIKE '%' + @ObjectName + '%'
				AND so.name <> @ObjectName
	
		WHILE @RefView is not null
		BEGIN
			SELECT @AllViews = @AllViews + @RefView + '; '
	
			-- ** go get the next view that references it
			SELECT @RefView = MIN(so.name) 
				FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
				WHERE so.type = 'V' AND sc.text LIKE '%' + @ObjectName + '%'
					AND so.name <> @ObjectName
					AND so.name > @RefView
		END
	
		-- ** Find Stored Procedures that reference each table
		SELECT @RefProc = MIN(so.name) 
			FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
			WHERE so.type = 'P' AND sc.text LIKE '%' + @ObjectName + '%'
				AND so.name <> @ObjectName
	
		WHILE @RefProc is not null
		BEGIN
			SELECT @AllProc = @AllProc + @RefProc + '; '
			-- ** go get the next procedure that references it
	
			SELECT @RefProc = MIN(so.name) 
				FROM sysobjects so INNER JOIN syscomments sc ON so.id = sc.id 
				WHERE so.type = 'P' AND sc.text LIKE '%' + @ObjectName + '%'
					AND so.name <> @ObjectName
					AND so.name > @RefProc
		END
		-- ** trim final semicolon & space from @AllViews and @AllProc
		IF @AllViews LIKE '%;%'
		BEGIN
	
			SELECT @AllViews = Substring(@AllViews, 1, Len(@AllViews) - 1)
		END
	
		IF @AllProc LIKE '%;%'
		BEGIN
			-- ** trim off the last semicolon & space
			SELECT @AllProc = Substring(@AllProc, 1, Len(@AllProc) - 1)
		END
		-- ** Update the Info Table with the list of views and stored procedures that reference the table; 
		-- ** 	update ONLY if the information is different than what's already there...
		IF EXISTS(SELECT * FROM INFO_DbObjects
				WHERE ObjectName = @ObjectName AND (RefByViews <> @AllViews OR RefByProc <> @AllProc))
		BEGIN
			PRINT ''
			PRINT '	Object Name: ' + @ObjectName
			PRINT '		Views: ' + @AllViews
			PRINT '		Procedures: ' + @AllProc
			UPDATE INFO_DbObjects
			SET 	 RefByViews = @AllViews
				, RefByProc = @AllProc
			WHERE ObjectName = @ObjectName
		END
		-- ** reset @AllViews and @AllProc
		SELECT @AllViews = ''
			, @AllProc = ''
		-- ** get the next table from Info Tables
		SELECT @ObjectName = MIN(ObjectName) FROM INFO_DbObjects WHERE ObjectDeletedYN = 0 AND ObjectName > @ObjectName
	END
END
PRINT ''
PRINT 'Update of database documentation completed'
GO