Join sysdatabases and sysobjects

• Anam Verma

  SSCertifiable

  Points: 5365

  More actions

  October 30, 2007 at 12:12 am

  #183297

  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.

• Oana Schuster

  SSCommitted

  Points: 1642

  More actions

  October 30, 2007 at 2:02 am

  #746496

  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.