Just a day ago, one of the Jr. Developer requested that if I can help her with finding one particular table in every database on SQL Server. We have many Database Server and on some of the Database Server we have nearly 200 databases on it. The requirement was to find out one particular table from all the database. This was not possible by visual inspection as it might take lots of time and human error was possible. She was aware of the system view sys.tables.
SELECT * FROM sys.Tables WHERE name LIKE '%Address%'
The limitation of query mentioned above is that it only searches in one database and user has to keep on changing database manually and run the query again. I wrote down following quick script which looks into all the database on the server and provides the database name, schema name and table containing searched word in its name.
CREATE PROCEDURE usp_FindTableNameInAllDatabase @TableName VARCHAR(256) AS DECLARE @DBName VARCHAR(256) DECLARE @varSQL VARCHAR(512) DECLARE @getDBName CURSOR SET @getDBName = CURSOR FOR SELECT name FROM sys.databases CREATE TABLE #TmpTable (DBName VARCHAR(256), SchemaName VARCHAR(256), TableName VARCHAR(256)) OPEN @getDBName FETCH NEXT FROM @getDBName INTO @DBName WHILE @@FETCH_STATUS = 0 BEGIN SET @varSQL = 'USE ' + @DBName + '; INSERT INTO #TmpTable SELECT '''+ @DBName + ''' AS DBName, SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables WHERE name LIKE ''%' + @TableName + '%''' EXEC (@varSQL) FETCH NEXT FROM @getDBName INTO @DBName END CLOSE @getDBName DEALLOCATE @getDBName SELECT * FROM #TmpTable DROP TABLE #TmpTable GO EXEC usp_FindTableNameInAllDatabase 'Address' GO
If you liked this script and have similar useful script let me know and I will post it here.
Reference : Pinal Dave (https://blog.sqlauthority.com)