February 11, 2009 at 4:14 am
I want to list all the tables from all the databases in a select query, without using any cursor.
Is it possible????????
Thanks
February 11, 2009 at 4:22 am
you can get the details from sys.objects or by running sp_tables.
select * from sys.objects where type = 'U
Regards,
Sriram
Sriram
February 11, 2009 at 5:32 am
You can use an undocumented stored procedure sp_msforeachdb:
EXEC sp_msforeachdb 'select * from sys.tables'
or, if you need one resulset:
select Replicate(' ', 128) DB, * into #temp from sys.tables
delete #temp
exec sp_msforeachdb 'use [?] insert #temp select ''?'', * from sys.tables '
select * from #temp
drop table #temp
February 11, 2009 at 5:39 am
sp_msforeachdb is a cursor.
You can use the INFORMATION_SCHEMA system views. Specifically INFORMATION_SCHEMA.TABLES. These are common between SQL Server 2000, 2005 and 2008, so are a much better place to program against than sys.objects, which changes a bit (and between 2000 & 2005, more than a bit) between all three versions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2009 at 5:42 am
Vic.K (2/11/2009)
You can use an undocumented stored procedure sp_msforeachdb:
EXEC sp_msforeachdb 'select * from sys.tables'or, if you need one resulset:
select Replicate(' ', 128) DB, * into #temp from sys.tables
delete #temp
exec sp_msforeachdb 'use [?] insert #temp select ''?'', * from sys.tables '
select * from #temp
drop table #temp
How about using
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from information_schema.tables
where table_type = 'BASE TABLE'
order by table_schema, table_name
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 11, 2009 at 5:50 am
ALZDBA (2/11/2009)
How about using
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from information_schema.tables
where table_type = 'BASE TABLE'
order by table_schema, table_name
But it's good only for the actual database, isn't it? So, there is no possibility to list all tables from all databases without cursors?
February 11, 2009 at 7:43 am
You could always union the same statement for each database.
SELECT *
FROM DB1.sys.sysobjects
UNION
SELECT *
FROM DB2.sys.sysobjects
February 11, 2009 at 7:49 am
Vic is correct. because there is no single repository for the tables/objects of all databases, you have to query each repository seperately...whether information_schema, sysobjects or sys.objects, you gotta do each seperately.
this is a situation where a cursor is a good thing in order to accomplish the task at hand.
Vic.K (2/11/2009)
But it's good only for the actual database, isn't it? So, there is no possibility to list all tables from all databases without cursors?
Lowell
February 11, 2009 at 8:18 am
Vic.K (2/11/2009)
ALZDBA (2/11/2009)
How about using
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from information_schema.tables
where table_type = 'BASE TABLE'
order by table_schema, table_name
But it's good only for the actual database, isn't it? So, there is no possibility to list all tables from all databases without cursors?
Indeed, you still have to qualify the database or run the query _in_ the database.
select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from mydb.information_schema.tables
where table_type = 'BASE TABLE'
order by table_schema, table_name
you might as well generate the whole sqlstatement to execute....
Declare @SQLStmt varchar(max)
Set @SQLStmt = ''
Select @SQLStmt = @SQLStmt
+ case @SQLStmt when '' then '' else ' union all ' end
+ 'select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
from ' + name + '.information_schema.tables
where table_type = ''BASE TABLE'' '
from master.sys.databases;
Print @SQLStmt
exec ( @SQLStmt )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 9, 2009 at 2:07 am
You rock ALZDBA....
Its working like M16 ..........:cool:
Thanks dude!!!
March 9, 2009 at 5:34 am
The old sp_tables works too 😀
March 9, 2009 at 6:19 am
Hi
Yet another way
[font="Courier New"]
DECLARE @sql NVARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql + CASE WHEN LEN(@sql) != 0 THEN 'UNION ALL ' ELSE '' END + 'SELECT * FROM ' + QUOTENAME(name) + '.sys.tables' + CHAR(13) + CHAR(10)
FROM sys.databases
WHERE name NOT IN ('tempdb', 'master', 'msdb', 'model')
--AND name LIKE 'Adventure%'
EXECUTE sp_executesql @sql
[/font]
Greets
Flo
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply