Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Join sysdatabases and sysobjects Expand / Collapse
Author
Message
Posted Tuesday, October 30, 2007 12:12 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:33 AM
Points: 527, Visits: 1,246
is there any way to join sysdatabases and sysobjects.

my requriment is to fetch all the database names, table names from a server. i thought to get this info by joining the above two data dictionaries but i can't find any common column to join on.

if there is any other way to get this info, please let me know.

any help is appreciated.
Post #416305
Posted Tuesday, October 30, 2007 2:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 17, 2013 1:42 AM
Points: 890, Visits: 451
Hi,

Try this :

CREATE TABLE #t(d SYSNAME, t SYSNAME);

EXEC sp_msForEachDB 'INSERT #t SELECT ''?'', TABLE_NAME
FROM [?].INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE=''BASE TABLE'';';

SELECT * FROM #t ORDER BY d,t;

DROP TABLE #t;

Thanks,
Oana.
Post #416340
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse