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
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
How can we identify a tables as a system table or a user-defined table?
SELECT * FROM SYS.schemas
SELECT * FROM SYS.TABLES WHERE [schema_id]=5 AND convert(Varchar,create_date,112)>=’20120701′
ORDER BY 1
can we add a where clause to find the databases with specific table .
eg sp_msforeachdb ‘select “?” AS db, name from [?].sys.tables where name = ”mytableName”’
(this didn’t work!)
Brilliant Pinal.
Allow me to modify it slightly to add the associated columns as well:
sp_msforeachdb ‘select “?” AS db, t.*, c.* from [?].sys.tables t, [?].sys.columns c where t.object_id = c.object_id’
This ought to help in compiling a Server specific Data Catalog of sorts.
how to get all data base list with name,size(MB,GB) through script in text file.
please reply………..
Dear all,
could you please anyone help me.how to get all data base list with name,size(MB,GB) through script in text file.
please reply………
hi can i get tables list of existed database in sql server according to the constraints (database model ) because it will not throw error when all scripts execute one by one or bulk .
Can we write a procedure having 3 input parameters
1)X Database Name
2)Y Database Name
3)Table Name
what I want as a out put is the columns which are not matching in the two databases with same table name.
I need syntax to search all tables for a company name.
Hello Sir,
sir can you identifying which table is temporary in database . I have fetching following issue get tables details
generate Error like :
The object ‘#A6AD16E2’ does not exist in database ‘tempdb’ or is invalid for this operation.
I have get all Tables List query like:
“use ” + databaseName + ” select name from sys.tables”
When i get tables details then generate Error
USE ” + databaseName + ” EXEC sp_spaceused #A6AD16E2″
The object ‘#A6AD16E2’ does not exist in database ‘tempdb’ or is invalid for this operation.
how can resolve this issue. any way to identifying temporary ?
Temporary tables in tempdb are allways temporary, if you can’t get names and sizes atomically, you won’t have the table waiting there to get its size. Imagine that this tables were used only for sorts.
Hi Pinal,
Could you please help me with query to find all the tables used by Stored Procedure across database?
I got the query in single database. If SP use tables across multiple database then I need to report it to query output…
have you tried sp_depends? or sys.sql_dependencies ?
The above script is does the job . i cannot copy the whole data at once as the results are seperated by databases . Kindly suggest the alternative .
You can look at SELECT INTO topic in books online.
Dear all , how can i retrieve all table in db to my Reports. Example :
Table 001
Table 002
Table 003
Table 004
*on screen*
i click Table 001 and it will display all the data n column in my report.
i click Table 004 and it will display all the data n column in my report.
*all the Table have different column
*i have 300 Table in my db