Find Table Usage

,

Creates a printable report of all stored procedures, views, triggers and user-defined functions that reference any of the tables passed into the procedure.

Wrote this to easily list items affected by table changes when working in a fast-paced development environment developing a shrink wrapped product undergoing many schema changes.

Split function is listed after sp definintion

--Check if proc exists before creating
IF EXISTS (SELECT name 
	   FROM   dbo.sysobjects 
	   WHERE  name = N'usp_FindTableUsage' 
	   AND 	  xtype = 'P')
    DROP PROCEDURE dbo.usp_FindTableUsage
GO

CREATE PROCEDURE dbo.usp_FindTableUsage
	@vcTableList varchar(8000) 
AS
/************************************************************************************************
DESCRIPTION:	Creates prinatable report of all stored procedures, views, triggers
		and user-defined functions that reference any of the
		tables passed into the proc.
	
PARAMETERS:
		@vcTableList - comma delimited list of table name
			for which usage is being sought	.	
	
REMARKS:	@vcTableList may not be greater than 8000 characters and
		there may not be any spaces between/after names and comma 
		delimiters.

		To print the output of this report in Query Analyzer select
		the execute mode to be file (icon just to left of check mark)
		and you will be prompted for a file name to save as. Alternately
		you can select the execute mode to be text, run the query, set
		the focus on the results pane and then select File/Save from
		the menu.

		The split function must be installed in the same database to
		use this procedure.  
		
		This procedure must be installed in the database where it will
		be run due to it's use of database system tables.
		
DEPENDENCIES:	dbo.split function

USAGE:	
   
  usp_FindTableUsage 'Order Details,Products,Suppliers'
	
AUTHOR:	Karen Gayda

DATE: 08/20/2004

MODIFICATION HISTORY:
WHO		DATE		DESCRIPTION
---		----------	-------------------------------------------
*************************************************************************************************/
SET NOCOUNT ON

--Create table to hold table names
DECLARE @tblTableArray TABLE 
   (
   	TableName	varchar(40)	
   )

-- load table names into array table
	INSERT INTO @tblTableArray
		SELECT Element FROM
			dbo.split(@vcTableList, ',')


PRINT ''
PRINT 'REPORT FOR TABLE DEPENDENCIES for TABLES:'
PRINT '-----------------------------------------'
PRINT  CHAR(9)+CHAR(9)+ REPLACE(@vcTableList,',',CHAR(13)+CHAR(10)+CHAR(9)+CHAR(9))


PRINT ''
PRINT ''
PRINT 'STORED PROCEDURES:'
PRINT ''
	SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Procedure Name]
		FROM sysobjects o
		INNER JOIN syscomments c
			ON o.ID = c.ID
		INNER JOIN @tblTableArray t
			ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
		WHERE 	o.XTYPE = 'P'
			AND o.NAME <> 'usp_FindTableUsage'
			
			
	ORDER BY t.TableName, [Procedure Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent stored procedures'

PRINT''
PRINT''
PRINT 'VIEWS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [View Name]
		FROM sysobjects o
		INNER JOIN syscomments c
			ON o.ID = c.ID
		INNER JOIN @tblTableArray t
			ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
		WHERE 	o.XTYPE = 'V'
			
	ORDER BY t.TableName, [View Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent views'

PRINT''
PRINT''
PRINT 'FUNCTIONS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Function Name]
		FROM sysobjects o
		INNER JOIN syscomments c
			ON o.ID = c.ID
		INNER JOIN @tblTableArray t
			ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
		WHERE 	o.XTYPE IN ('FN','IF','TF')
			
	ORDER BY t.TableName, [Function Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent functions'

PRINT''
PRINT''
PRINT 'TRIGGERS:'
PRINT''
SELECT DISTINCT t.TableName , SUBSTRING(o.NAME,1,60) AS [Trigger Name]
		FROM sysobjects o
		INNER JOIN syscomments c
			ON o.ID = c.ID
		INNER JOIN @tblTableArray t
			ON c.Text LIKE '%[ ,=]' + t.TableName + '[ .,]%'
		WHERE 	o.XTYPE = 'TR'
			
	ORDER BY t.TableName, [Trigger Name]
PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent triggers'

RETURN (0)

Error_Handler:
	RETURN(-1)
GO
GRANT EXECUTE ON [dbo].usp_FindTableUsage TO PUBLIC
GO

IF exists (SELECT * from dbo.sysobjects 
	WHERE id = object_id(N'[dbo].[Split]') 
	AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[Split]
GO


GO
CREATE FUNCTION dbo.Split (	@vcDelimitedString 		varchar(8000),
				@vcDelimiter			varchar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
		delimiter points.  Returns the individual items as a table data
		type with the ElementID field as the array index and the Element
		field as the data

PARAMETERS:
		@vcDelimitedString		- The string to be split
		@vcDelimiter			- String containing the delimiter where
							delimited string should be split

RETURNS:
		Table data type containing array of strings that were split with
		the delimiters removed from the source string

USAGE:
		SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID

AUTHOR:	Karen Gayda

DATE: 	05/31/2001

MODIFICATION HISTORY:
	WHO		DATE		DESCRIPTION
	---		----------	---------------------------------------------------

***************************************************************************/
RETURNS @tblArray TABLE 
   (
	ElementID	smallint	IDENTITY(1,1),  --Array index
   	Element		varchar(1000)			--Array element contents
   )
AS
BEGIN

	DECLARE 
	@siIndex					smallint,
	@siStart					smallint,
	@siDelSize					smallint


	SET @siDelSize	= LEN(@vcDelimiter)
	--loop through source string and add elements to destination table array
	WHILE LEN(@vcDelimitedString) > 0
	BEGIN
		SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
		IF @siIndex = 0
		BEGIN
			INSERT INTO @tblArray VALUES(@vcDelimitedString)
			BREAK
		END
		ELSE
		BEGIN
			INSERT INTO @tblArray VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
			SET @siStart = @siIndex + @siDelSize
			SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
		END
	END
	
	RETURN
END
GO

Rate

3 (6)

Share

Share

Rate

3 (6)