Find table name in all objects of all databases

Question

I have a system with several databases and client applications. All databases are in the same instance of SQL Server. They were developed by different people at different times. Therefore, if some error occurs, it is difficult to find in which procedure or to initiate a data change.

Now I use this script that I found on this site:

SELECT DISTINCT ISNULL(sd.referenced_schema_name+'.','')+ OBJECT_NAME(sd.referenced_id)TableName, OBJECT_NAME(sd.referencing_id)Ref_Object, CASE WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable')= 1 THEN'Table' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction')= 1 THEN'Function' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTableFunction')= 1 THEN'Function' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsScalarFunction')=1 THEN'Function' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsTrigger')= 1 THEN'Trigger' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsView')= 1 THEN'View' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsUserTable')= 1 THEN'Table' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsProcedure')= 1 THEN'Procedure' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsIndexed')= 1 THEN'Index' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsForeignKey')= 1 THEN'ForeignKey' WHEN OBJECTPROPERTYEX(sd.referencing_id,N'IsPrimaryKey')= 1 THEN'PrimaryKey' END AS Ref_Object_Name FROM sys.sql_expression_dependencies SD INNER JOIN sys.objects obj ON obj.object_id=sd.referenced_id WHERE obj.is_ms_shipped= 0 and referenced_id=object_id('TABLE_NAME') /*Where one can Replace table Name*/ AND obj.type_desc='USER_TABLE' ORDER BY TableName,Ref_Object,Ref_Object_Name 

But this script only works for the database to which the table belongs.

I want to get a list of object names (or even better for an object) for all objects of all databases in which the specified table name occurs:

 Database_Name SchemaName ObjectName ObjectKind 

Thanks.

Answer 1

 DECLARE @table_name SYSNAME = N'%'; DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += 'SELECT DISTINCT Database_Name = ''' + QUOTENAME(name) + ''', COALESCE(sd.referenced_schema_name +''.'', '''')+ o.name AS TableName, r.name AS Ref_Object, r.type_desc AS Ref_Object_Name FROM ' + QUOTENAME(name) + '.sys.sql_expression_dependencies AS sd INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o ON o.object_id = sd.referenced_id INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS r ON sd.referencing_id = r.object_id WHERE o.is_ms_shipped = 0 and referenced_id = o.object_id AND o.type_desc = ''USER_TABLE'' AND o.name LIKE ''' + @table_name + ''' UNION ALL ' FROM sys.databases WHERE database_id BETWEEN 5 AND 32766; SET @sql = LEFT(@sql, LEN(@sql)-11) + 'ORDER BY Database_Name, TableName,Ref_Object,Ref_Object_Name'; EXEC sp_executesql @sql; 

EDIT

The above links will find all links in each database, but will not find links to different databases. It took a little time, and the result is not quite what you wanted, but I think it makes it more understandable:

 DECLARE @table_name SYSNAME = N'%'; -- find all CREATE TABLE #d ( db SYSNAME, [object_id] INT, sch SYSNAME, obj SYSNAME, ref_db NVARCHAR(128), ref_sch NVARCHAR(128), ref_obj NVARCHAR(128), ref_object_id INT, type_desc SYSNAME ); DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += 'SELECT ''' + QUOTENAME(name) + ''', d.referencing_id, QUOTENAME(s.name), QUOTENAME(o.name), QUOTENAME(d.referenced_database_name), QUOTENAME(d.referenced_schema_name), QUOTENAME(d.referenced_entity_name), d.referenced_id, o.type_desc FROM ' + QUOTENAME(name) + '.sys.sql_expression_dependencies AS d INNER JOIN ' + QUOTENAME(name) + '.sys.objects AS o ON d.referencing_id = o.[object_id] INNER JOIN ' + QUOTENAME(name) + '.sys.schemas AS s ON o.[schema_id] = s.[schema_id] WHERE d.referenced_entity_name LIKE ''' + @table_name + ''' UNION ALL ' FROM sys.databases WHERE database_id BETWEEN 5 AND 32766; SET @sql = LEFT(@sql, LEN(@sql)-11); INSERT #d EXEC sp_executesql @sql; SELECT db+'.'+sch+'.'+obj, ' (' + type_desc + ') references => ', COALESCE(ref_db, db)+'.'+ref_sch+'.'+ref_obj FROM #d; GO DROP TABLE #d; GO 

Output Example:

 [db1].[dbo].[foo] (SQL_STORED_PROCEDURE) references => [db2].[dbo].[bar] [db1].[dbo].[xyz] (SQL_STORED_PROCEDURE) references => [db1].[dbo].[table_xyz] 

Answer 2

It will help you get started.

 create table ##tbData ( DatabaseName Varchar(64), objectName varchar(128), ObjectKind varchar(128) ) go EXEC sp_Msforeachdb "use [?]; insert ##tbData select db_name(),so.name,so.xtype from sysobjects so" select * from ##tbdata 

Essentially create the table and SQL statement that you want to use, then use the undocumented sp_MSforEachdb to load the table from each database

Answer 3

You can look at something like sp_MSForeachdb and invoke the above query for each of these databases.