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)
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?
Hello!
I have over 700 databases on my server and have a task to identify each database with a column named “Checked_Out”. This means I need to somehow query the system tables for database name then subquery for table.column = “Checked_Out”.
Thanks, Teri
As far as I can tell. None of these queries show both master relevent tables and sys.schema local DB tables. Is there a short and easy query to enumerate ALL databases and ALL tables in both the master and also local to each DB? Hope that sounded clear.
The query doesnt tell in what database the field is belongs to.
Dear all,
i have one problem in ssrs.i need to display sales report customer wise [sales and budget and variance and variance percentage] from different databases but in single server.it will be there detailes in multiple databases.i need to display detailes in single report.could you please anyone help me.for example we are maintaining 2010,2011 databases so i need to display customer no,sales,budget,variance,variance percentage in single report …
year from 2012 year to 2013 date filter 01-01-12..31-12-13
Customer No. | Sales -2012 |Budget-2012 |Variance-2012 |Variance %-2012 Sales -2013 |Budget-2013| Variance-2013 |Variance %-2013
i need details like this in one report using multiple databases.please anyone help me.
navya krishna katta