Introduction

As all of us do when confronted with a problem, the resource of choice is to ‘Google it’.

This is where the plot thickens. Recently I was asked to stage data from numerous databases which were to be loaded into a data warehouse. To make a long story short, I was looking for a manner in which to obtain the table names from each database, to ascertain potential overlap.

As the source data comes from a SQL database created from dumps of a third party product,  one could say that there were +/- 95 tables for each database.

Yes I know that first instinct is to use the system stored procedure “exec sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'”.

However, if one stops to think about this, it would be nice to have all the results in a temporary or disc based  table; which in itself , implies additional labour.

This said,  I decided to ‘re-invent’ the wheel. The full code sample may be found at the bottom of this article.

Define a few temporary tables and variables

declare @SQL varchar(max);

declare @databasename varchar(75)

/*

drop table ##rawdata3

drop table #rawdata1

drop table #rawdata11

*/

-- A temp table to hold the names of my databases

CREATE TABLE #rawdata1

(

   database_name varchar(50) ,

   database_size varchar(50),

   remarks Varchar(50)

)

--A temp table with the same database names as above, HOWEVER using an

--Identity number (recNO) as a loop variable.

--You will note below that I loop through until I reach 25 (see below) as at

--that point the system databases, the reporting server database etc begin.

--1- 24 are user databases. These are really what I was looking for.

--Whilst NOT the best solution,it works and the code was meant as a quick

--and dirty.

CREATE TABLE #rawdata11

(

   recNo int identity(1,1),

   database_name varchar(50) ,

   database_size varchar(50),

   remarks Varchar(50)

)

--My output table showing the database name and table name

CREATE TABLE ##rawdata3

(

   database_name varchar(75) ,

   table_name varchar(75),

)

Insert the database names into a temporary table

I pull the database names using the system stored procedure sp_databases

INSERT INTO #rawdata1

EXEC sp_databases

Go

Insert the results from #rawdata1 into a table containing a record number  #rawdata11 so that I can LOOP through the extract

INSERT into #rawdata11

select * from  #rawdata1

We now declare 3 more variables:

 @kounter is used to keep track of our position within the loop.

@databasename is used to keep track of the’ current ‘ database name being used in the current pass of the loop;  as inorder to obtain the tables for that database we  need to issue a ‘USE’ statement, an insert command and other related code parts. This is the challenging part.

@sql is a varchar(max) variable used to contain the ‘USE’ statement PLUS the’ insert ‘ code statements.

We now initalize @kounter to 1 .

declare @kounter int;

declare @databasename varchar(75);

declare @sql varchar(max);

set @kounter = 1

The Loop

The astute reader will remember that the temporary table #rawdata11 contains our  database names  and each ‘database row’ has a record number (recNo). I am only interested in record numbers under 25. I now set the value of the temporary variable @DatabaseName (see below) .Note that I used the row number as a part of the predicate.

Now, knowing the database name, I can create dynamic T-SQL to be executed using the sp_sqlexec stored procedure (see the code in red below).

Finally, after all the tables for that given database have been placed in temporary table ##rawdata3, I increment the counter and continue on.

Note that I used a global temporary table to ensure that the result set persists after the termination of the run.

At some stage, I plan to redo this part of the code, as global temporary tables are not really an ideal solution.

 WHILE (@kounter < 25)

 BEGIN

 select @DatabaseName = database_name from #rawdata11 where recNo = @kounter

 set @SQL = 'Use ' + @DatabaseName +

' Insert into ##rawdata3 ' +

+ ' SELECT table_catalog,Table_name FROM information_schema.tables'

exec sp_sqlexec  @Sql

 SET @kounter  = @kounter + 1

 END

The full code extract

Here is the full code sample.

declare @SQL varchar(max);

declare @databasename varchar(75)

/*

drop table ##rawdata3

drop table #rawdata1

drop table #rawdata11

*/

CREATE TABLE #rawdata1

(

   database_name varchar(50) ,

   database_size varchar(50),

   remarks Varchar(50)

)

CREATE TABLE #rawdata11

(

   recNo int identity(1,1),

   database_name varchar(50) ,

   database_size varchar(50),

   remarks Varchar(50)

)

CREATE TABLE ##rawdata3

(

   database_name varchar(75) ,

   table_name varchar(75),

)

INSERT INTO #rawdata1

EXEC sp_databases

go

INSERT into #rawdata11

select * from  #rawdata1

declare @kounter int;

declare @databasename varchar(75);

declare @sql varchar(max);

set @kounter = 1

WHILE (@kounter < 25)

 BEGIN

 select @databasename = database_name from #rawdata11 where recNo = @kounter

 set @SQL = 'Use ' + @DatabaseName +

' Insert into ##rawdata3 ' +

+ ' SELECT table_catalog,Table_name FROM information_schema.tables'

exec sp_sqlexec  @Sql

 SET @kounter  = @kounter + 1

 END

 select * from ##rawdata3

 where table_name like '%SalesOrderHeader%'