Today we will go over very simple script which will list all the tables for all the database.
sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'
Update: Based on comments received below I have updated this article. Thank you to all the readers. This is good example where something small like this have good participation from readers.
Reference : Pinal Dave (http://www.SQLAuthority.com)
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
44 Comments. Leave new
LOL!
It took me half of a dozen lines to come up with what your one liner achieved!
Good one!
-Marlon Ribunal
how this query was used in production enviourment
when it was used?
what purpose itz used?
Hi Pinal,
Shouldn’t we be using the information schema views in Sql 2005 and onwards?
sp_msforeachdb @command1=’USE ?;SELECT * FROM INFORMATION_SCHEMA.Tables’
Nice post Pinal!
You can avoid the “USE” statement by using three part object name. For example:
sp_msforeachdb ‘select * from ?.sys.tables’
And some times it is important to know which database is being processed and the following might be helpful.
sp_msforeachdb ‘select ”?” AS db, * from ?.sys.tables’
Thank you Jacob Sir!
Is there a way to use this query for auditing DMl events
Thank you ,
Sometimes a database may contain the name space
sp_msforeachdb ‘select “?” AS db, * from [?].sys.tables’
Hi,
Im new to SQL server, i was wondering if there was anyway, we could list all tables for a single database along with the column names.
Im finding this really hard so any help would be appreciated
Hi Jyotsna,
You will get all Table’s columns by this query:
select t.name As TableName, c.Column_Name
from sys.tables t
INNER JOIN information_schema.columns c ON c.Table_Name = t.Name
ORDER BY t.name
Thanks,
Tejas
SQLYoga.com
where the statistics of query i.e cost ,time of exe. etc are stored??(in which table?)
in which table query statistics are stored?
Hello Chaitanya,
Statistics is stored in statblob column in sys.indexes table.
Regards,
Pinal dave
thnx a ton…
How to write a script to truncate tables using sp_msForEachDB. Please help.
I am using:
DECLARE @Table1 NVARCHAR(100)
DECLARE @Table2 NVARCHAR(100)
DECLARE @Table3 NVARCHAR(100)
SET @Table1=’fwkErrorLog’
SET @Table2=’fwkUserAuthenticationLog’
SET @Table3=’fwkUserAuthenticationLogArchive’
DECLARE @dbname NVARCHAR(500)
DECLARE @sqlStatement NVARCHAR(500)
DECLARE @singlequote char(4)
SET @singlequote=””
SET @sqlStatement= ‘USE ?; IF EXISTS (SELECT ”x” FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘ +
+ @singlequote+@Table1+@singlequote
+’) TRUNCATE table ‘+@Table1
EXEC sp_msForEachDB @sqlStatement
SET @sqlStatement= ‘USE ?; IF EXISTS (SELECT ”x” FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘ +
+ @singlequote+@Table2+@singlequote
+’) TRUNCATE table ‘+@Table2
EXEC sp_msForEachDB @sqlStatement
SET @sqlStatement= ‘USE ?; IF EXISTS (SELECT ”x” FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘ +
+@singlequote+@Table3+@singlequote
+’) TRUNCATE table ‘+@Table3
EXEC sp_msForEachDB @sqlStatement
Can it be much better
How can i search all procedures within a database for a specific table name?
How i can find which table has Created from which User when a Data base is used by Different Login.plz guide me.
How can i search all the tables in a particular database.
select * from information_schema.tables
How do I use the output of the above command as input into another query. For instance, I want to get all the tables with a specific column name then use that result to further query each table to get the data for that column. Something like (DB2 syntax):
select * from (select tabname from syscat.columns where colname = ‘my_column_name’);
Of course that only gives me the table names, and not what I want…
Colin,
if it’s still interesting for you :
exec sp_MSforeachtable ‘if COLUMNPROPERTY(object_id(”?”),”XXXXX”,”ColumnID”) is not null select ”?” [Table],* from ?’
Replace XXXXX with your column name.
this is great tips. => sp_msforeachdb ‘select “?” AS db, * from [?].sys.tables’
But How to capture those output and use it into query statement ?becasue i need information for all those database names and all table names to export those data to text files using BPC.
Create a table with same strcuture of what sp_msforeachdb returns. Then use this statement
insert into #t(column_list)
EXEC sp_msforeachdb ‘select “?” AS db, * from [?].sys.tables’
How can I find stored procedures that have never been executed. Not only in the time the log persists in memory? Any idea?