Searching for a Column or Table in Multiple Databases? Here is an easy way!!

Hi Guys
Today while working on a requirement, I was searching for a specific table in my entire SQL Server instance. My Instance contains around 75 databases, and searching manually is not a good idea,  seriously. To find a object in a specific database (not in the whole instance), first thing that comes to any developer's mind is to use System Objects like sys.tables or sys.columns. But again executing and checking the results using below system query on all 75 databases again involves manual effort.


-- System Objects Based Query
SELECT T.Name AS TableName, C.Name AS ColumnName 
FROM sys.tables T
INNER JOIN sys.columns C ON T.Object_ID = C.Object_ID
ORDER BY T.Name

So thought of using CURSOR at-least here. Developer's hate it for its performance but there are certainly few situations where you would like to use it. Here is the solution in the form of CURSOR I designed that picks every table, column and the concerned database to which they belong.


-- Cursor that loops over all the databases in a SQL Server instance
DECLARE @NAME VARCHAR(50)
DECLARE @SQL VARCHAR(500)
DECLARE @DATABASENAME CURSOR
SET @DATABASENAME = CURSOR FOR
SELECT name FROM sys.databases
OPEN @DATABASENAME
FETCH NEXT FROM @DATABASENAME INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'USE '+ @NAME + 
' SELECT ''' + @NAME + ''' AS [Database], T.name AS TableName, C.name AS ColumnName
FROM sys.tables T
INNER JOIN sys.columns C ON T.object_id = C.object_id'
EXEC (@SQL)
FETCH NEXT FROM @DATABASENAME INTO @NAME
END
CLOSE @DATABASENAME
DEALLOCATE @DATABASENAME
GO

If you want to exclude the four system databases to be avoided from querying, you can make use of a filter with WHERE database_id NOT IN (1, 2, 3, 4)

Hope it is useful for those who work on multiple databases with lot of objects in there. :)