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
Or you could built in functionality that SQL Server provides and use 1 line of code
sp_msforeachdb “SELECT ‘?’ DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE ‘%address%'”
If you did indeed want to keep the results in a table you could do that too
CREATE TABLE #TableNameResults (DatabaseName VARCHAR(100) NOT NULL, TableName VARCHAR(100) NOT NULL)
INSERT INTO #TableNameResults EXEC sp_msforeachdb “SELECT ‘?’ DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE ‘%address%'”
SELECT * FROM #TableNameResults
DROP TABLE #TableNameResults
i think the above query could be enhanced by using contains or freetext
because its returning every thing related to the search word. but if we want to return only the word that we have specified.. for example
instead of returning every thing related to address it should return only adress
How about a query that writes queries? Using sysdatabases table in the MASTER database you can write a query that looks for a table name in the sysobjects table in each database. In my case I’m looking for a ‘Folders’ table.
Running this query…
USE MASTER; SELECT ‘USE ‘ + name + ‘; SELECT ”’ + name + ”’ as DBName, name as TableName FROM sysobjects where name like ”Folders”’ FROM sysdatabases WHERE dbid > 5 ORDER BY dbid
You get these queries…
USE Northwind; SELECT ‘Northwind’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
USE Q_SanJoaquin; SELECT ‘Q_SanJoaquin’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
USE ClerkConv; SELECT ‘ClerkConv’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
USE QuestysEnt; SELECT ‘QuestysEnt’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
USE Q_Police; SELECT ‘Q_Police’ as DBName, name as TableName FROM sysobjects where name like ‘Folders’
And running those queries, you get this….
DBName TableName
——— ——————————————————————————————————————————–
(0 row(s) affected)
DBName TableName
———— ——————————————————————————————————————————–
Q_SanJoaquin Folders
(1 row(s) affected)
DBName TableName
——— ——————————————————————————————————————————–
(0 row(s) affected)
DBName TableName
———- ——————————————————————————————————————————–
QuestysEnt Folders
(1 row(s) affected)
DBName TableName
——– ——————————————————————————————————————————–
Q_Police Folders
(1 row(s) affected)
Your articles are really very conducive to solve my problems.
Is there a way to query an entire sql server looking for a certain column? am trying to create an application where the users can search our entire sql server by a column name.I want the query to list the table name,database name,column name,column data type.
Any ideas or sample code will be greatly appreciated.
Thanks much..
@Praveen
— Either make this a stored procedure or change value of @Column_Name,
Code Starts here,
Declare @Column_Name sysname
Set @Column_Name = ‘eid’
Declare @temp_variable table ([Database Name] sysname, [Schema Name] sysname, [Table Name] sysname, [Column Name] sysname , [Ordinal Position]Int, [Is Null] varchar(3), [Data type] varchar(50), [Length] int , [Precision] Int, [Scale] Int)
Declare @Sqlcmd1 nvarchar(4000)
Set @Sqlcmd1 =
‘EXEC sp_msforeachdb
”
USE ?
Select TABLE_CATALOG [Database Name]
,TABLE_SCHEMA [Schema Name]
,TABLE_NAME [Table Name]
,COLUMN_NAME [Column Name]
,ORDINAL_POSITION [Ordinal Position]
,IS_NULLABLE [Is Null]
,DATA_TYPE [Data Type]
,CHARACTER_MAXIMUM_LENGTH [Lenth]
,NUMERIC_PRECISION [Precision]
,NUMERIC_SCALE [Scale]
From Information_Schema.columns
Where COLUMN_NAME = ””’+@Column_Name+””””
Insert into @temp_variable Exec Sp_ExecuteSQL @Sqlcmd1
Select * from @temp_variable
~ IM.
Can we find in all the stored procedures where the table is used?
If we alter the table and add a column, and want to see where all the table is used in the stored procedures and functions.
How do we achieve that.
@Sandya
Exec sp_depends ‘table_name’
Result set returned, will contain all dependencies, like which other tables, views, stored procedure depend on this table
~IM
I want to know how to see all tables of particular database.
What is the SQL query for that.
Pinal Dave,
Can please tell me how to get the order of table created based on the relationship created.
for Example : 1. Master_table
2. Transaction_Table
Regards
Arun PK
Hi, I am using this sproc and it works fine. I want to extend it so that it will also drop those dbs.
I am having a problem with that.
Would you please give me a hint?
Thanks,
Little correction in my above post.
I want to generate separate script that reads the dbname and table name from the table and delete it.
So, I would not drop temp table in 1st sproc.
how to find total no of table in a database
I am trying to create a sql script to query mutliple databases that contain the same exact tables. I want to create a table to have a column for the database name and a table for a specific column of a table. I am getting this error Msg
208, Level 16, State 1, Line 34
Invalid object name ”+ @name +’.dbo.tbldoc’.
the @name is set as:
set @name=(‘select name from sys.databases where [name] like ”Z%”’)
declare IMAGEC cursor for
select count(pgcount) as ImageCount from [‘+ @name +’].dbo.tbldoc
Hi All!
how to find a particular Table view is used in more than one query?????
Hi Pinal,
I need a store procedure…
Say there are 26 databases in a server:
o NAccountA
o NAccountB
o NAccountC
o …
o NAccountX
o NAccountY
o NAccountZ
And every database has a table Employee with fields Name and Salary.
Please write a Stored Procedure to get the total employee and salary of each databases (including total at the bottom of the result). The output should be:
Database Employee Count Total Salary
NAccountA 200 $25,180.00
NAccountB 180 $18,860.00
… … …
NAccountZ 180 $18,860.00
Total: 2300 $562,520.00
use this
EXECUTE sp_msforeachdb ‘USE ? SELECT *
FROM sys.Tables
WHERE name LIKE ”%LuceneCollection%”’
How about finding table with ## in front of table name?
for example ‘##tablename’
You need o quey in tempdb database
Already find!!!
USE TEMPDB
GO
SELECT Table_Catalog, Table_Name FROM information_schema.tables
WHERE table_name like ‘%TEMP%’
use master
SELECT DISTINCT DB_NAME(database_id) FROM [sys].[dm_db_index_operational_stats](NULL,NULL,NULL,NULL)
WHERE OBJECT_NAME(object_id,database_id) = ‘TableName’
select count(*) From information_schema.tables
where table_type = ‘base table’
Work perfect to get count of table in a database..