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)
42 Comments. Leave new
See also this tip that avoids the loop
One minor addition I would suggest is that you should only look at tables where state_desc = ‘ONLINE’.
Where would you place this line?
i have a problem can you please help;
it is on sql server 2008. in a single database i have 10 tables, in each table there is column “asset_id”, now, let us say there are 4 tables where asset_it=’ddn224′, how do i generate a list of table where asset_id =’ddn224′.
nice
search table in one db sp_Tables ‘%Address%’
Thanks for the post, We can use sp_msforeachdb to get the same result in single line of code.
Hi,
We are using SQL Server 2012. In a database there are so many tables are existing, but some tables only using in our organisation Remaining are not using .So how to find only some tables which are using in one database?
Hi,
I am new to my organisation, They assigned me a work that to prepare a document of our database backup policy and database designing. So how do i start that assignment?
Please help Me?
From the above query if we need to get the data for particular user i.e; selecting the user from the front end application then how can we write the query
Thanks…this helps!
my pleasure.
declare @SQL nvarchar(max) = ”
select @SQL = @SQL + ‘SELECT ”’ + d.name + ”’ as DatabaseName, t.name as TableName, c.name as ColumnName FROM ‘ + d.name + ‘.sys.tables t inner join ‘ + d.name + ‘.sys.columns c ON c.object_id = t.object_id WHERE c.name LIKE ”CIT%” AND t.Name LIKE ”tbP%” union all ‘
from sys.databases d
where d.name not in(‘master’, ‘tempdb’, ‘msdb’, ‘model’, ‘ReportServer’, ‘ReportServerTempDB’)
set @SQL = left(@SQL, len(@SQL) – 10)
exec sp_executesql @SQL
In our environment we have more than 50 databases, but this script fetched details of only 12 databases. So, Pinal could you help here please.
Hi,
Please how do I make it possible to search database name too, Like making @DBname searchable as @TableName.
Thanks
You can use the same script to search for database name as well. No change required.
CREATE PROCEDURE usp_FindTableNameInAllDatabase
@TableName VARCHAR(256),
@DatabaseName Varchar(max)
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 ([‘+ @DBName + ‘] LIKE ”[‘+ @DatabaseName +’]” AND name LIKE ”’+ @TableName+”’)’;
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
DROP TABLE #TmpTable
GO
But when I execute the proc
EXEC usp_FindTableNameInAllDatabase ‘Logins’,’EXTRA_122-126ChanceryLane’;
I get this Error Message
Msg 207, Level 16, State 1, Line 50
Invalid column name ‘master’.
Please me look into in this, Have tried different ways
does it work for db name having space e.g. db name : “Demo 123”; i am getting error
did you figure this out ?
select [name] as [database_name] from sys.databases
where
case when state_desc = ‘ONLINE’
then object_id(quotename([name]) + ‘.[dbo].[version]’, ‘U’)
end is not null
order by 1
This too similar useful script.
To Find databases containing particular table in SQL Server database
Piotr Kononow – Dataedo