Query to list all tables for a server along with the associated databases

• JP-686566

  SSC Enthusiast

  Points: 150

  More actions

  November 18, 2008 at 5:38 pm

  #126895

  Query to list all tables for a server along with the associated databases

  Thanks

  JP

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  November 18, 2008 at 6:32 pm

  #900237

  Try this.

  Create Table #Tmp (dbname sysname, tablename sysname)

  go

  sp_msforeachdb 'use ?; Insert into #tmp select ''?'' dbname, name from sys.tables'

  Select * from #Tmp

  Drop Table #Tmp

• JP-686566

  SSC Enthusiast

  Points: 150

  More actions

  November 18, 2008 at 7:00 pm

  #900244

  Thanks a lot

  I googled it and found the same :).

  But do have any idea to use something like

  use @DBNAME

  go

  select name from sys.Tables

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  November 18, 2008 at 7:45 pm

  #900256

  Here is another way. You cannot use a variable name in the USE Statement.

  Declare @sql varchar(max)

  Set @sql = ''

  select @sql = 'Select ''' + name + ''' dbname, name tablename from ' + name + '.sys.tables Union ALL ' + @sql from sys.databases

  Set @sql = SUBSTRING(@SQL,1,LEN(@SQL)-10)

  EXEC(@SQL)

• Paresh Prajapati

  SSCertifiable

  Points: 7567

  More actions

  November 18, 2008 at 9:47 pm

  #900287

  create table tablelist (DBName varchar(1000),TableName varchar(4000))

  GO

  DECLARE @sql varchar(8000)

  DECLARE @BAK_PATH VARCHAR(4000)

  declare c_bak cursor FAST_FORWARD FOR

  select name from sys.databases

  OPEN c_bak

  FETCH NEXT FROM c_bak INTO @BAK_PATH

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @sql = 'insert into tablelist select '''+@BAK_PATH+''',name from '+@BAK_PATH +'.sys.sysobjects where xtype =''U'''

  PRINT (@SQL)

  EXEC(@SQL)

  FETCH NEXT FROM c_bak INTO @BAK_PATH

  END

  CLOSE C_BAK

  DEALLOCATE C_BAK

  _____________________________________________________________________________________________________________
  Paresh Prajapati
  ➡ +919924626601
  http://paresh-sqldba.blogspot.com/[/url]
  LinkedIn | Tweet Me | FaceBook | Brijj

• JP-686566

  SSC Enthusiast

  Points: 150

  More actions

  November 19, 2008 at 12:08 pm

  #900672

  Thanks a lot..:)

  Can you tell me like how can i pass database as a variable

  i.e when i am do the following, i get this error Msg 137, Level 15, State 2, Line 3

  Must declare the scalar variable "@db_name".

  create procedure sp_select_db_tables

  @db_name varchar(50)

  as

  Create Table #Tmp (dbname sysname, tablename sysname)

  go

  sp_msforeachdb 'use ?; Insert into #tmp select ''?'' dbname, name from sys.tables'

  Select * from #Tmp where dbname = @db_name

  Drop Table #Tmp

  go

• Timothy J Hartford

  SSCarpal Tunnel

  Points: 4214

  More actions

  November 20, 2008 at 9:22 am

  #901118

  This is one of the few places where I use cursors...

  Here is a few excerpts from a change monitoring sp I wrote (it looks at more than just tables):

  DECLARE @DBName VARCHAR(500)

  DECLARE @sql VARCHAR(MAX)

  DECLARE DBLoop CURSOR FOR

  SELECT DEV.name

  FROM master.sys.Databases DEV

  ...

  OPEN DBLoop

  FETCH NEXT FROM DBLoop INTO @DBName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  -- Insert Dev Server DB Objects

  --------------------------------------------------

  SET @sql = 'USE [' + @DBName + ']

  INSERT INTO [SERVER].dbo.tDatabaseObjects

  SELECT ...

  ''' + @DBName + '''

  , S.name AS SchemaName

  , O.name

  , O.object_id

  , O.type

  , O.create_date

  , O.modify_date

  , NULL

  , NULL

  FROM ' + @DBName + '.sys.Objects O

  LEFT JOIN ' + @DBName + '.sys.Schemas S

  ON O.[schema_id] = S.[schema_id]

  WHERE type NOT IN (''C'' /* CHECK constraint */

  , ''D'' /* DEFAULT (constraint or stand-alone) */

  , ''F'' /* FOREIGN KEY constraint */

  , ''PK'' /* PRIMARY KEY constraint */

  , ''PC'' /* Assembly (CLR) stored procedure */

  , ''RF'' /* Replication-filter-procedure */

  , ''S'' /* System base table */

  , ''TA'' /* Assembly (CLR) DML trigger */

  , ''TR'' /* SQL DML trigger */

  , ''UQ'' /* UNIQUE constraint */

  , ''X'' /* Extended stored procedure */

  , ''IT'' /* Internal table */)'

  EXEC (@SQL)

  --------------------------------------------------

  ...

  FETCH NEXT FROM DBLoop INTO @DBName

  END

  CLOSE DBLoop

  DEALLOCATE DBLoop

  ...

• JP-686566

  SSC Enthusiast

  Points: 150

  More actions

  November 20, 2008 at 1:59 pm

  #901282

  Thanks a lot