-- This script should be run on a database where a searched object resides.
-- The script then looks at all the code on the current database and all other
-- databases on the server, and searches for all references to the object.
SET NOCOUNT ON
DECLARE @ObjectName NVARCHAR(128)
DECLARE @ScriptMsg NVARCHAR(512)
DECLARE @DBName SYSNAME
DECLARE @ServerName SYSNAME
DECLARE @CurrentObjectName NVARCHAR(128)
DECLARE @RowsCnt INT
DECLARE @CurrentIdx INT
DECLARE @tmpStr NVARCHAR(256)
DECLARE @RowStr NVARCHAR(256)
DECLARE @StartCommentCnt INT
DECLARE @CurrentDBNameIdx INT
DECLARE @CurrentDBName NVARCHAR(128)
DECLARE @CurrentObjectOwner NVARCHAR(128)
DECLARE @DBCnt INT
SET @ObjectName = 'sysobjects'
-- strip out the '[' and ']' characters from the table and column names
IF RIGHT(@ObjectName, 1) = ']'
SET @ObjectName = LEFT(@ObjectName, LEN(@ObjectName) - 1)
IF LEFT(@ObjectName, 1) = '['
SET @ObjectName = RIGHT(@ObjectName, LEN(@ObjectName) - 1)
-- Get the DB and server information
SET @ServerName = CAST(SERVERPROPERTY('MachineName') AS SYSNAME)
SET @DBName = db_name()
-- Check that the provided object name indeed exist in the database
IF @ObjectName IS NULL OR @ObjectName = ''
BEGIN
SET @ScriptMsg = 'The object name provided in the variable @ObjectName is either null or empty. Please provide a valid column name.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
IF NOT EXISTS(
SELECT *
FROM sysobjects WITH (NOLOCK)
WHERE [name] = @ObjectName)
BEGIN
SET @ScriptMsg = N'The object ' + @ObjectName + N' does not exists in the ' + @DBName + N' database, on the ' + @ServerName + N' server.'
RAISERROR(@ScriptMsg, 16, 1)
RETURN
END
-- Define the temp tables that will hold the column references within the database
IF OBJECT_ID('tempdb..#tblDatabases', 'U') IS NOT NULL
DROP TABLE #tblDatabases
IF OBJECT_ID('tempdb..#tblDependentObject', 'U') IS NOT NULL
DROP TABLE #tblDependentObject
IF OBJECT_ID('tempdb..#tblTextEdit', 'U') IS NOT NULL
DROP TABLE #tblTextEdit
CREATE TABLE #tblDatabases (Idx INT IDENTITY(1, 1), DBName NVARCHAR(128))
CREATE TABLE #tblDependentObject (
DatabaseName NVARCHAR(128),
ObjectName NVARCHAR(128),
ObjectType NVARCHAR(128),
ObjectOwner NVARCHAR(128),
ObjectID INT,
ParentObject NVARCHAR(128))
-- This tables holds the text of the code of the dependent objects scanned.
-- SQLText is 512 and not 256 (as dictated by the sp_helptext command)
-- due to the string processing performed below.
CREATE TABLE #tblTextEdit (Idx INT IDENTITY(1, 1), SQLText NVARCHAR(512))
-- Get all databases on the server
INSERT INTO #tblDatabases (DBName)
SELECT name
FROM master.dbo.sysdatabases WITH (NOLOCK)
SET @DBCnt = @@ROWCOUNT
SET @CurrentDBNameIdx = 1
WHILE @CurrentDBNameIdx <= @DBCnt
BEGIN
SELECT @CurrentDBName = DBName
FROM #tblDatabases
WHERE Idx = @CurrentDBNameIdx
-- First, get all candidate objects: These objects are ones
-- that contain the object name in any form,
-- i.e., ones that contain '%
INSERT INTO #tblDependentObject (
DatabaseName,
ObjectName,
ObjectID,
ObjectType,
ObjectOwner,
ParentObject)
EXEC(' USE [' + @CurrentDBName + ']
SELECT DISTINCT N''' + @CurrentDBName + ''' AS DBName,
OBJECT_NAME(a.[id]),
a.[id],
b.xtype,
USER_NAME(b.uid),
CASE
WHEN b.parent_obj <= 0 OR b.parent_obj IS NULL THEN ''--''
ELSE OBJECT_NAME(parent_obj)
END
FROM syscomments a WITH (NOLOCK)
INNER JOIN sysobjects b WITH (NOLOCK)
ON a.id = b.id
WHERE ([text] LIKE N''%' + @ObjectName + N'%'')
and USER_NAME(b.uid) <> ''system_function_schema'' ')
SET @CurrentDBNameIdx = @CurrentDBNameIdx + 1
END
-- For each database that possibly references the object, we run the following code:
-- For each candidate object - perform a thorough analysis:
-- 1. Strip out all the comments in the code
-- 2. Check that only valid characters that may follow/preceed the table and
-- column names indeed do so. This is used to minimize false positives.
SET @CurrentDBNameIdx = 1
SET @CurrentDBName = NULL
WHILE @CurrentDBNameIdx <= @DBCnt
BEGIN
SELECT @CurrentDBName = DBName
FROM #tblDatabases
WHERE Idx = @CurrentDBNameIdx
SELECT @CurrentObjectName = MIN(ObjectName)
FROM #tblDependentObject WITH (NOLOCK)
WHERE DatabaseName = @CurrentDBName
SET @CurrentObjectOwner = NULL
SELECT @CurrentObjectOwner = ObjectOwner
FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
AND DatabaseName = @CurrentDBName
-- Search the text of each referencing database object
WHILE @CurrentObjectName IS NOT NULL
BEGIN
IF @CurrentObjectOwner IS NOT NULL
INSERT INTO #tblTextEdit (SQLText)
EXEC (' USE [' + @CurrentDBName + '] EXEC sp_helptext ''[' + @CurrentObjectOwner + '].[' + @CurrentObjectName + ']'' ')
ELSE
INSERT INTO #tblTextEdit (SQLText)
EXEC (' USE [' + @CurrentDBName + '] EXEC sp_helptext ''[' + @CurrentObjectName + ']'' ')
SET @RowsCnt = @@ROWCOUNT
-- Now, we gather information about all '/*' and '*/' instances in the code,
-- and try to remove all these comments from the code.
-- This is not simple to do, especially not in a recordset friendly manner,
-- since it is possible that there are nested /* comments that start in the same
-- line (returned from sp_helptext), or are spanned over several lines.
-- The easiest way to do it, although tedious, is to scan all text entries in the
-- code. Every instace of /* increments a counter by 1, every instance of
-- */ decrements the counter by 1. The code between /* and */ is not considered
-- for dependency detection.
-- Find the initial scan position
SET @CurrentIdx = NULL
SET @StartCommentCnt = 0
SELECT TOP 1 @CurrentIdx = Idx,
@tmpStr = SQLText
FROM #tblTextEdit WITH (NOLOCK)
WHERE SQLText LIKE '%/*%'
ORDER BY Idx ASC
-- If the code does not contains any /* */ comments - don't get into the
-- while loop below. The purpose of this while loop is to filter
-- all comments that are bounded by /* */ characters.
IF @CurrentIdx IS NULL
SET @CurrentIdx = @RowsCnt + 1
-- Some explanations about this loop: From the first row that contains
-- the /* characters, every time we see a /* we increment a couter by 1.
-- While the counter is > 0 then we are inside a comment. The counter
-- is decremented upon each */, so that nested comments are supported.
-- This way, @tmpStr is used to process the text in each code row,
-- and @RowStr is the actual non-commented code. Each row in #tblTextEdit
-- is updated with the non-commented row @RowStr, in each loop of the WHILE
-- command, so that the after the loop, #tblTextEdit does no longer contain
-- the code comments of type /* */.
WHILE @CurrentIdx <= @RowsCnt
BEGIN
SET @RowStr = N''
-- Process all characters in current text line.
WHILE @tmpStr <> '' AND @tmpStr IS NOT NULL
BEGIN
IF LEFT(@tmpStr, 2) = '/*'
BEGIN
SET @StartCommentCnt = @StartCommentCnt + 1
SET @tmpStr = SUBSTRING(@tmpStr, 3, LEN(@tmpStr))
SET @RowStr = @RowStr + N' '
END
ELSE
BEGIN
IF LEFT(@tmpStr, 2) = '*/'
BEGIN
SET @tmpStr = SUBSTRING(@tmpStr, 3, LEN(@tmpStr))
IF @StartCommentCnt > 0
BEGIN -- the first line is new
SET @RowStr = @RowStr + N' '
SET @StartCommentCnt = @StartCommentCnt - 1
END
END
ELSE
BEGIN
IF @StartCommentCnt = 0
SET @RowStr = @RowStr + SUBSTRING(@tmpStr, 1, 1)
SET @tmpStr = SUBSTRING(@tmpStr, 2, LEN(@tmpStr))
END
END
END
-- Update the processed row without its comments
UPDATE #tblTextEdit
SET SQLText = @RowStr
WHERE Idx = @CurrentIdx
-- Start processing the row
SET @CurrentIdx = @CurrentIdx + 1
SELECT TOP 1 @tmpStr = SQLText
FROM #tblTextEdit WITH (NOLOCK)
WHERE Idx = @CurrentIdx
END
-- Now, we don't strip out comments that start with -- since those
-- can be referenced within PRINT or EXEC statements which may be needed.
-- This may result in false positives, but that is acceptible, given our
-- objective.
-- Last, it is possible that a word is cut off between consecutive lines in
-- sp_helptext. For this reason, if a row in #tblTextEdit ends with a character
-- then we append the first set of characters from the next line to it.
UPDATE a
SET a.SQLText = CASE WHEN LEN(b.SQLText) < 255 THEN a.SQLText + b.SQLText
WHEN CHARINDEX(' ', b.SQLText) > 0 THEN a.SQLText + LEFT(b.SQLText, CHARINDEX(' ', b.SQLText) - 1)
ELSE a.SQLText
END
FROM #tblTextEdit a
INNER JOIN #tblTextEdit b
ON a.Idx + 1 = b.Idx
WHERE SUBSTRING(a.SQLText, 255, 1) <> ''
AND (LEN(b.SQLText) < 255 OR CHARINDEX(' ', b.SQLText) > 0)
-- Finally, we have the raw code. Now, if the column is still referenced
-- inside the code, and is preceeded/followed by valid characters only,
-- then the object is reported as one that references the table column.
--
-- Characters that may preceed an object name are:
-- CHAR(0)-CHAR(31), ' ' (i.e., CHAR(32)), ! (i.e., CHAR(33)), '"' (i.e., CHAR(34)),
-- '%' (i.e., CHAR(37)), '&' (i.e., CHAR(38)), ''' (i.e., CHAR(39)),
-- '(' (i.e., CHAR(40)), ')' (i.e., CHAR(41)), '+' (i.e., CHAR(43)),
-- ',' (i.e., CHAR(44)), '-' (i.e., CHAR(45)), '.' (i.e., CHAR(46)),
-- '<' (i.e., CHAR(60)), '=' (i.e., CHAR(61)), '>' (i.e., CHAR(62)),
-- '[' (i.e., CHAR(91)), '^' (i.e., CHAR(94)), '|' (i.e., CHAR(124))
--
-- After an object name, we can only have one of the following characters:
-- CHAR(0)-CHAR(31), ' ' (i.e., CHAR(32)), '"' (i.e., CHAR(34))
-- '%' (i.e., CHAR(37)), ''' (i.e., CHAR(39)), ')' (i.e., CHAR(41)),
-- '+' (i.e., CHAR(43)), ',' (i.e., CHAR(44)), '-' (i.e., CHAR(45)),
-- '.' (i.e., CHAR(46)), ';' (i.e., CHAR(59)), '<' (i.e., CHAR(60)),
-- '=' (i.e., CHAR(61)), '>' (i.e., CHAR(62)), ']' (i.e., CHAR(93)),
-- '^' (i.e., CHAR(94)), '|' (i.e., CHAR(124))
--
-- Note: CHAR(93) cannot be used in a straightforward manner, hence the query below.
IF NOT EXISTS(
SELECT *
FROM #tblTextEdit WITH (NOLOCK)
WHERE (SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName
+ N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(34) + CHAR(37) + CHAR(39) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(59) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(94) + CHAR(124) + N']' + N'%'
OR SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName
OR SQLText LIKE
+ N'%' + N'[' + CHAR(0) + CHAR(1) + CHAR(2) + CHAR(3) + CHAR(4) + CHAR(5) + CHAR(6) + CHAR(7) + CHAR(8) + CHAR(9) + CHAR(10) + CHAR(11) + CHAR(12) + CHAR(13) + CHAR(14) + CHAR(15) + CHAR(16) + CHAR(17) + CHAR(18) + CHAR(19) + CHAR(20) + CHAR(21) + CHAR(22) + CHAR(23) + CHAR(24) + CHAR(25) + CHAR(26) + CHAR(27) + CHAR(28) + CHAR(29) + CHAR(30) + CHAR(31) + CHAR(32) + CHAR(33) + CHAR(34) + CHAR(37) + CHAR(38) + CHAR(39) + CHAR(40) + CHAR(41) + CHAR(43) + CHAR(44) + CHAR(45) + CHAR(46) + CHAR(60) + CHAR(61) + CHAR(62) + CHAR(91) + CHAR(94) + CHAR(124) + N']'
+ @ObjectName + ']%'))
BEGIN
DELETE FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
END
SELECT @CurrentObjectName = MIN(ObjectName)
FROM #tblDependentObject WITH (NOLOCK)
WHERE ObjectName > @CurrentObjectName
AND DatabaseName = @CurrentDBName
SET @CurrentObjectOwner = NULL
SELECT @CurrentObjectOwner = ObjectOwner
FROM #tblDependentObject
WHERE ObjectName = @CurrentObjectName
AND DatabaseName = @CurrentDBName
-- To keep things clean, start the identity in #tblTextEdit from 1
-- We use DELETE and not TRUNCATE, since the user may not have
-- TRUNCATE permissions.
DELETE FROM #tblTextEdit
DBCC CHECKIDENT('#tblTextEdit', RESEED, 0)
END
SET @CurrentDBNameIdx = @CurrentDBNameIdx + 1
END
UPDATE #tblDependentObject
SET ObjectType = CASE
WHEN ObjectType = 'C' THEN 'CHECK constraint'
WHEN ObjectType = 'D' THEN 'Default or DEFAULT constraint'
WHEN ObjectType = 'F' THEN 'FOREIGN KEY constraint'
WHEN ObjectType = 'L' THEN 'Log'
WHEN ObjectType = 'FN' THEN 'Scalar function'
WHEN ObjectType = 'IF' THEN 'Inlined table-function'
WHEN ObjectType = 'P' THEN 'Stored procedure'
WHEN ObjectType = 'PK' THEN 'PRIMARY KEY constraint'
WHEN ObjectType = 'RF' THEN 'Replication filter stored procedure'
WHEN ObjectType = 'S' THEN 'System table'
WHEN ObjectType = 'TF' THEN 'Table function'
WHEN ObjectType = 'TR' THEN 'Trigger'
WHEN ObjectType = 'U' THEN 'User table'
WHEN ObjectType = 'UQ' THEN 'UNIQUE constraint'
WHEN ObjectType = 'V' THEN 'View'
WHEN ObjectType = 'X' THEN 'Extended stored procedure'
ELSE 'Unknown'
END
SET NOCOUNT OFF
SELECT DatabaseName,
ObjectName,
ObjectType,
ObjectID,
ParentObject
FROM #tblDependentObject WITH (NOLOCK)
ORDER BY 1 ASC
GO
Geen opmerkingen:
Een reactie posten