Find Table Usage

--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:
WHODATEDESCRIPTION
--------------------------------------------------------
*************************************************************************************************/SET NOCOUNT ON

--Create table to hold table names
DECLARE @tblTableArray TABLE 
   (
   TableNamevarchar(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),
@vcDelimitervarchar(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:
WHODATEDESCRIPTION
----------------------------------------------------------------

***************************************************************************/RETURNS @tblArray TABLE 
   (
ElementIDsmallintIDENTITY(1,1),  --Array index
   Elementvarchar(1000)--Array element contents
   )
AS
BEGIN

DECLARE 
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint


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