usp_FindTableUsage

USE [DBeheer]
GO
/****** Object: StoredProcedure [dbo].[usp_FindTableUsage] Script Date: 11/25/2008 13:58:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE PROCEDURE [dbo].[usp_FindTableUsage]

AS

SET NOCOUNT ON

DECLARE @vcTableList VARCHAR(8000)
SET @vcTableList = ''

SELECT @vcTableList = COALESCE(@vcTableList+ ', ', '') + name
from sysobjects where type='U'


--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'


PRINT''
PRINT''
PRINT 'JOBS:'
PRINT''
DECLARE @table_name SYSNAME;
SELECT @table_name=Element FROM
dbo.split(@vcTableList, ',');

SELECT
j.name,
s.step_name,
s.command
FROM
msdb.dbo.sysjobs j
INNER JOIN
msdb.dbo.sysjobsteps s
ON
j.job_id = s.job_id
WHERE
s.command LIKE '%' + @table_name + '%';


PRINT CAST(@@ROWCOUNT as Varchar(5)) + ' dependent jobs'


RETURN (0)

Error_Handler:
RETURN(-1)

-------------------------------------------------------------
USE [DBeheer]
GO
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 11/25/2008 14:03:17 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split] ( @vcDelimitedString varchar(8000),
@vcDelimiter varchar

(100) )

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



-------------------------------------------------------------

exec usp_FindTableUsage