Technical Article

List any Object In Any Database On Server

,

I found my self in a fimilar situation where I wanted to modify a table but was not sure how heavily it was used. So I started out with a script that just searched for a table. Then I tought to my self...self...
What about all those times you want to expand a column or drop it all together? So I created this little sript that will look for what ever you pass in.

CREATE PROCEDURE spFindObjectUsage
 @ObjectToFindNVARCHAR(100) = ''
,@ResultMessageVARCHAR(200) = '' OUTPUT
AS
DECLARE
 @ReturnCodeINT
,@StringToExecuteNVARCHAR(1500)
,@DBToProcessINT
,@ServerNameVARCHAR(200)
,@TableWithServerVARCHAR(200)
,@NameOfDatabaseVARCHAR(50)
--
DECLARE@ServerDatabaseTables TABLE
(TempTblIDINT NOT NULL IDENTITY(1,1)
,DBNameVARCHAR(100) NOT NULL DEFAULT ''
,ProcessedBIT NOT NULL DEFAULT 0)
--
CREATE TABLE #ServerDatabaseObjectUsage 
(UsageIDINT NOT NULL IDENTITY(1,1)
,DBNameVARCHAR(100) NOT NULL DEFAULT ''
,ObjectUsedInVARCHAR(200) NOT NULL DEFAULT ''
,TypeOfObjectVARCHAR(50)  NOT NULL DEFAULT ''
,IsColumnOfTable BIT NOT NULL DEFAULT 0)
--
-- first get all the databases on the current server
--
INSERT INTO @ServerDatabaseTables
(DBName)
SELECT
name
FROM master.dbo.sysdatabases
--
SET @TableWithServer = ''
SET @NameOfDatabase  = ''
--
SET NOCOUNT ON
-- each database has it's own listing of System Objects so inorder to get
-- a correct listing we will need to go through every database.
-- the only way I know to do this is using sqlexec.
-- I know it is not the best way but we will need the ability to dynamically
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)
--
SELECT   @ServerName   = DBName + '.dbo.'
,@NameOfDatabase  = DBName
FROM @ServerDatabaseTables
WHERE TempTblID = @DBToProcess
--
SET @StringToExecute =  'INSERT INTO #ServerDatabaseObjectUsage ' +
        '(DBName' +
',ObjectUsedIn' +
',TypeOfObject' + 
',IsColumnOfTable) ' +
'SELECT DISTINCT ' +
char(39)+@NameOfDatabase+char(39)+ 
',obj.NAME' + 
',(CASE obj.XTYPE  WHEN ' + char(39) + 'P' + char(39) + 
 ' THEN ' + char(39) + 'PROCEDURE' + char(39) +
 ' WHEN ' + char(39) + 'V' + char(39) + 
 ' THEN ' + char(39) + 'VIEW' + char(39) +
 ' WHEN ' + char(39) + 'U' + char(39) + 
 ' THEN ' + char(39) + 'USER TABLE' + char(39) +
 ' WHEN ' + char(39) + 'D' + char(39) + 
 ' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
 ' WHEN ' + char(39) + 'F' + char(39) + 
 ' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
 ' WHEN ' + char(39) + 'IF' + char(39) + 
 ' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
 ' WHEN ' + char(39) + 'FN' + char(39) + 
 ' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
 ' WHEN ' + char(39) + 'TF' + char(39) + 
 ' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
 ' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +
'THEN 1 ELSE 0 END)' + 
'FROM ' + @ServerName + 'sysobjects as obj ' + 
'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' + 
'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' + 
'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + 
' OR syscol.name LIKE '  + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + 
' ORDER BY obj.name '
SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))
PRINT LEN(@StringToExecute)
--
exec sp_executesql @StringToExecute
--
IF (@@ERROR != 0) BEGIN
SET @ReturnCode = 1
GOTO END_PROCEDURE
END
--
UPDATE @ServerDatabaseTables
SET Processed = 1
WHERE TempTblID = @DBToProcess
END
--
SELECT * FROM #ServerDatabaseObjectUsage
--
DROP TABLE #ServerDatabaseObjectUsage
--
SET @ReturnCode = 0

END_PROCEDURE:
IF (@ReturnCode != 0) BEGIN
SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))
END ELSE BEGIN
SET @ResultMessage = 'OK'
END
RETURN @ReturnCode

Read 973 times
(1 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating