List out all objects from all the databases by using column string

Question

Hi,

Could you please help on general Tsql query to fetch all the objects from all the databases by using specific string(column name)?

Thanks.

Answer 1

Try: (assuming you have permissions for all databases, you may need to filter some of them):

declare @Sql nvarchar(max), @columnName VARCHAR(30) = 's%';

select @Sql = 


(select '
SELECT ' + QUOTENAME(name,'''') + ' as [DB Name], table_schema as [Schema Name], table_name as [Table Name], [column_name] from ' + 
QUOTENAME(Name) + '.INFORMATION_SCHEMA.columns
WHERE column_name like @columnName
order by [DB Name],[Schema Name], [Table Name], [column_name];' from sys.databases
order by name FOR XML PATH(''), TYPE).value('.', 'varchar(max)')
PRINT @SQL;

EXECUTE sp_executeSQL @SQL, N'@columnName varchar(30)', @columnName = @columnName;

Answer 2

Hi @Vivaan Shresth
You can use system catalog view sys.objects to view all objects in a SQL database.
You can also use SSMS in-built object search functionality to find out specific objects across all online databases in SQL instance.

For more details , please refer to this document: Different ways to search for objects in SQL databases

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.