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
 @ObjectToFind	NVARCHAR(100) = ''
,@ResultMessage	VARCHAR(200) = '' OUTPUT
AS
DECLARE
	 @ReturnCode		INT
	,@StringToExecute	NVARCHAR(1500)
	,@DBToProcess		INT
	,@ServerName		VARCHAR(200)
	,@TableWithServer	VARCHAR(200)
	,@NameOfDatabase	VARCHAR(50)
--
DECLARE	@ServerDatabaseTables TABLE
(TempTblID	INT NOT NULL IDENTITY(1,1)
,DBName		VARCHAR(100) NOT NULL DEFAULT ''
,Processed	BIT NOT NULL DEFAULT 0)
--
CREATE TABLE #ServerDatabaseObjectUsage 
(UsageID		INT NOT NULL IDENTITY(1,1)
,DBName			VARCHAR(100) NOT NULL DEFAULT ''
,ObjectUsedIn		VARCHAR(200) NOT NULL DEFAULT ''
,TypeOfObject		VARCHAR(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

Rate

Share

Share

Rate