April 7, 2009 at 8:55 am
Hi, any one can help me how to select all the tables from all the databases using t-sql?
I am trying like this joins:
Select d.name, t.tablename from sys.databases d inner join sys.tables t on t.db_id=d.db_id
but in sys.tables it wont retrieve db name or ID.
Can Some onehelp me??
April 7, 2009 at 9:02 am
how about his:
exec sp_MSForEachdb 'SELECT ''?'' as DBName, ?.dbo.sysobjects.name AS TableName from ?.dbo.sysobjects WHERE ?.dbo.sysobjects.xtype=''U'''
Lowell
April 7, 2009 at 9:30 pm
You can either use {DB_Name}.INFORMATION_SCHEMA.TABLES or create a view like this:
CREATE View vwAllTables as
Select 'DBOne' as [DatabaseName], * From [DatabaseOne].sys.Tables
UNION ALL Select 'DBTwo' as [DatabaseName], * From [DatabaseOne].sys.Tables
UNION ALL Select 'DBThree' as [DatabaseName], * From [DatabaseOne].sys.Tables
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 7, 2009 at 10:34 pm
This will do what you want:
declare @a nvarchar(max)
select
@a = isnull(@a+N' union all ',N'')+
'
select [Database] = convert(sysname,'''+a.name+
'''), [Table] = a.name collate SQL_Latin1_General_CP1_CI_AS from '+
quotename(a.name)+'.sys.tables a
'
from
sys.databases a
exec ( @a+ ' order by 1,2' )
Results:
Database Table
AdventureWorks Address
AdventureWorks AddressType
AdventureWorks AWBuildVersion
AdventureWorks BillOfMaterials
AdventureWorks Contact
...
April 8, 2009 at 12:44 am
Thank you very much.
This is what i need.
Great work!!! Thanks 🙂
Can u pls explain how this works? Because we are using different collations. Why we have to mention the collation details?
April 10, 2009 at 4:15 am
Hi,
It's good query, but it not works in SQL 2000
Is there any query, which will give the same outpur and can use in SQL 2000
April 10, 2009 at 5:14 am
Hi Kailash,
In 2000,
Use the information_schema.tables to get the table list with in DB,
For all DB
Try this
DECLARE ALLDB CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE dbid > 4
OPEN ALLDB
DECLARE @Stmt NVARCHAR(100)
DECLARE @DB NVARCHAR(10)
FETCH NEXT FROM ALLDB INTO @DB
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Stmt = N'USE ' + @DB + CHAR(13)+
N'SELECT * FROM information_schema.tables'
EXEC sp_executesql @Stmt
FETCH NEXT FROM ALLDB INTO @DB
END
CLOSE ALLDB
DEALLOCATE ALLDB
ARUN SAS
April 10, 2009 at 5:37 am
Kailash Mishra (4/10/2009)
Hi,It's good query, but it not works in SQL 2000
Is there any query, which will give the same outpur and can use in SQL 2000
You can use the View trick in SQL 2000 also. Just change the target table names from "{DatabaseName1}.sys.tables" to "{DatabaseName1}.dbo.sysobjects Where type = 'U'".
Note: there is a limit to how many different databases you can include in one view/query and that limit is lower for SQL Server 2000 (though I cannot recall exactly what they are).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2009 at 6:50 am
Hi
Since your count of databases is not too much you can use a VARCHAR(8000) and Barry's suggestion to use the INFORMATION_SCHEMA.TABLES:
DECLARE @sql VARCHAR(8000)
SELECT @sql = CASE WHEN @sql IS NULL
THEN 'SELECT '
ELSE @sql + 'UNION ALL SELECT ' END +
'''' + name + ': '', TABLE_NAME FROM ' + name + '.INFORMATION_SCHEMA.TABLES' +
CHAR(13) + CHAR(10)
FROM sysdatabases
EXECUTE (@sql)
If VARCHAR(8000) is too small for the dynamic SQL statement you can still use Lowell's solution with the sp_msforeachdb and fill a temp table with the information.
Greets
Flo
April 10, 2009 at 7:49 am
Kailash Mishra (4/10/2009)
Hi,It's good query, but it not works in SQL 2000
Is there any query, which will give the same outpur and can use in SQL 2000
the query i posted will work in all versions; in SQL7/2000 sysobjects is a table, but in sql2005/2008, it is a view, but exists in all versions.
exec sp_MSForEachdb 'SELECT ''?'' as DBName, ?.dbo.sysobjects.name AS TableName from ?.dbo.sysobjects WHERE ?.dbo.sysobjects.xtype=''U'''
Lowell
April 10, 2009 at 8:46 am
Florian Reischl (4/10/2009)
HiSince your count of databases is not too much you can use a VARCHAR(8000) and Barry's suggestion to use the INFORMATION_SCHEMA.TABLES:
Unfortunately Flo, the INFORMATION_SCHEMA views do not work cross-database in SQL Server 2000. You don't get an error, but you always get the view back for your current database. So they will only work with the USE statement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2009 at 10:00 am
RBarryYoung (4/10/2009)
Florian Reischl (4/10/2009)
HiSince your count of databases is not too much you can use a VARCHAR(8000) and Barry's suggestion to use the INFORMATION_SCHEMA.TABLES:
Unfortunately Flo, the INFORMATION_SCHEMA views do not work cross-database in SQL Server 2000. You don't get an error, but you always get the view back for your current database. So they will only work with the USE statement.
Using it cross database works OK for me on SQL 2000.
use tempdb
select tempdb_tables = table_name
from
information_schema.tables
select pubs_tables = table_name
from
pubs.information_schema.tables
Results:
tempdb_tables
-----------------------
sysconstraints
syssegments
(2 row(s) affected)
pubs_tables
-----------------------
authors
discounts
dtproperties
employee
jobs
pub_info
publishers
roysched
sales
stores
sysconstraints
syssegments
titleauthor
titles
titleview
April 10, 2009 at 10:18 am
RBarryYoung (4/10/2009)
Florian Reischl (4/10/2009)
HiSince your count of databases is not too much you can use a VARCHAR(8000) and Barry's suggestion to use the INFORMATION_SCHEMA.TABLES:
Unfortunately Flo, the INFORMATION_SCHEMA views do not work cross-database in SQL Server 2000. You don't get an error, but you always get the view back for your current database. So they will only work with the USE statement.
Hi Barry!
Thanks for your feedback!
Usually if you disagree with my suggestions I know that I'm wrong ;-). But in this case I tried twice on my small SS2k test server. It works fine (since there are not too much databases and VARCHAR(8000) is enough ).
Greets
Flo
PS: Already noticed the questions about your "complete dark side" in the other thread! 😀
April 10, 2009 at 10:20 am
Hmm (scratches head). Well, I must be misremembering then. Sorry.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 10, 2009 at 10:27 am
RBarryYoung (4/10/2009)
Hmm (scratches head). Well, I must be misremembering then. Sorry.
Don't say sorry! Thank you for the feedback and all the help in other threads! 🙂
Greets
Flo
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply