List usage of any object in any DB on Server
-
Hello, I posted this over at sqlserverentral.com but have not had any reply's. If you are a member here is the link: Code[^] What this will do: Allows you to show the usage of any table, procedure, column, or view in any database on the server where the code is running. Example: exec spFindObjectUsage 'MyTableOfParts' This will return all the views and procedures that call out MyTableOfParts. Here is the code:
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)+ -
Hello, I posted this over at sqlserverentral.com but have not had any reply's. If you are a member here is the link: Code[^] What this will do: Allows you to show the usage of any table, procedure, column, or view in any database on the server where the code is running. Example: exec spFindObjectUsage 'MyTableOfParts' This will return all the views and procedures that call out MyTableOfParts. Here is the code:
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)+